Hello guys,
Good day.
Today I will briefly show you how to convert the run_date and run_time columns from the msdb.dbo.sysjobhistory database catalog table to datetime. Currently, the run_date column is a varchar in the format yyyymmdd (Ex: 07 / 05 / 2015 = 20150507), and the run_time column is a time in the format hmmss (Ex: 08: 27: 00 = 82700). You can even understand visually what these values mean, but the calculation with these dates and times is much more complicated.
To make it easier to query this information, we can combine these 2 columns and convert them to datetime. I will demonstrate two ways on how to do this:
Using the msdb.dbo.agent_datetime system function
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT j.name, h.step_id, h.step_name, h.run_status, h.message, [RunDateTime] = msdb.dbo.agent_datetime(h.run_date, h.run_time), h.run_date, h.run_time FROM [msdb].[dbo].[sysjobs] j JOIN [msdb].[dbo].sysjobhistory h ON j.job_id = h.job_id WHERE h.run_status = 0 AND h.step_id = 0 |
Creating a custom function
Although there is already a system function to do this (Thanks to Caroline goltara, for the tip), many DBA's do not like to grant access to system functions for analysts, even though they know what function it does. To get around this, we can create a custom role and give analysts access to that role.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | USE [Util] GO CREATE FUNCTION [dbo].[fncJobs_Converte_Datetime] ( @DATE INT, @TIME INT ) RETURNS datetime AS BEGIN DECLARE @Date_Time datetime DECLARE @Ds_Date VARCHAR(8) = @DATE DECLARE @Ds_Time VARCHAR(8) = @TIME IF (@DATE = 0) RETURN NULL SET @Ds_Time = RIGHT('000000'+@Ds_Time,6) SET @Ds_Time = SUBSTRING(@Ds_Time,1,2)+':'+SUBSTRING(@Ds_Time,3,2)+':'+SUBSTRING(@Ds_Time,5,2) SET @Date_Time = CAST(@Ds_Date + ' ' + @Ds_Time AS datetime) RETURN @Date_Time END |
And now, let's use the function created to display the results:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT j.name, h.step_id, h.step_name, h.run_status, h.message, [RunDateTime] = Util.dbo.fncJobs_Converte_Datetime(h.run_date, h.run_time) FROM [msdb].[dbo].[sysjobs] j JOIN [msdb].[dbo].sysjobhistory h ON j.job_id = h.job_id WHERE h.run_status = 0 AND h.step_id = 0 |
The end result is exactly the same using both functions, as in the image below:
Oops… Ball show
I was looking for exactly that ..
system function saved ..
abras!