Job dates made easy

Our good friends at Microsoft warn us all the time to stay away from the SQL system tables, but do we listen? No. Sometimes you just need information you can't get anywhere else, like (for instance) the date and time a SQL Agent job step runs (from msdb..sysjobhistory) or the time and date that a job is next scheduled (from msdb..sysjobschedules).

All well and good, but for their own undoubtedly sound reasons the architects at Microsoft decided once upon a time that these date and time values would be stored as integers in separate int columns, instead of in one datetime column. Even in SQL Server 2008, where separate date and time datatypes are available, the columns are defined as integers.

This makes comparisons and checks on these tables trickier than it otherwise should be. You can't just simply query on all job history from the past seven days, for example...unless you've got a function like this one:


create function [dbo].[fnGetJobDateAsDate]

(@p_JobDate int,
@p_JobTime int)

returns datetime

as
begin

-- Code © 2009 360Data

declare @v_DateStr nvarchar(48)
declare @v_Year int
declare @v_Month int
declare @v_Day int
declare @v_Hour int
declare @v_Min int
declare @v_Sec int
declare @v_JobDate datetime

select @v_Year = (@p_JobDate / 10000)
select @v_Month = (@p_JobDate - (@v_Year * 10000)) / 100
select @v_Day = (@p_JobDate - (@v_Year * 10000) - (@v_Month * 100))

select @v_Hour = (@p_Jobtime / 10000)
select @v_Min = (@p_Jobtime - (@v_Hour * 10000)) / 100
select @v_Sec = (@p_Jobtime - (@v_Hour * 10000) - (@v_Min * 100))

select @v_DateStr = convert(nvarchar(4), @v_Year) + N'-' +
convert(nvarchar(2), @v_Month) + N'-' +
convert(nvarchar(4), @v_Day) + N' ' +
replace(convert(nvarchar(2), @v_Hour) + N':' +
convert(nvarchar(2), @v_Min) + N':' +
convert(nvarchar(2), @v_Sec), ' ', '0')

select @v_JobDate = convert(datetime, @v_DateStr)

return @v_JobDate

end

The function requires date and a time parameter integer input in YYYYMMDD / HHMMSS format, just like in the msdb system tables, and returns a regular datetime value. Try it:

SELECT top 50 step_name, run_date, run_time, [YourDatabaseNameHere].[dbo].[fnGetJobDateAsDate] (run_date, run_time)
from msdb..sysjobhistory
order by run_date desc, run_time desc;

Result: happiness all around.