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, by the hint), many DBAs don't like to give analysts access to system functions, even knowing what that function does. To get around this, we can create a custom function and allow access to it for analysts.
| 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!