CREATE VIEW dbo.SysJobHistory2 AS SELECT JH.* , CAST((CAST(JH.run_date / 10000 AS CHAR(4)) + '-' + RIGHT('0' + CAST(((JH.run_date/100) % 100) AS varchar(2)), 2) + '-' + RIGHT('0' + CAST(JH.run_date % 100 AS varchar(2)), 2) + 'T' + RIGHT('0' + CAST(JH.run_time/10000 AS varchar(2)), 2) + ':' + RIGHT('0' + CAST(((JH.run_time/100) % 100) AS varchar(2)), 2) + ':' + RIGHT('0' + CAST(JH.run_time % 100 AS varchar(2)), 2)) AS datetime) AS run_datetime FROM dbo.sysjobhistory JH
A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.
Friday, June 24, 2011
Making dates out of SQL Agent history
I started this post to simply have a reference piece of code on how to combine the run_date and run_time values from the msdb.dbo.sysjobhistory. I could have saved a great deal of time had I known to look at Working With SQL Server Agent Dates/Times (but of course there wouldn't have been the satisfaction of having written this code) The problem is that SQL Server stores dates and times as integer values (YYYYMMDD and HHMMSS) respectively. There isn't a method for concatenating those values into something and casting/converting it into a datetime value. Except there is and it's called msdb.dbo.agent_datetime(@date, @time). My solution was to create a jobhistory view with my code inline while MS provided a scalar function to do the same. An inline TVF might provide better performance but really with the tsql string mashing performance isn't going to be good either way.