SQL – Function to convert integer date to datetime format

Problem

I have an internally-developed metadata repository for the SQL Server instances I administer.  One of the metrics I track is based upon Job History success and failure.  This information comes directly from the msdb..sysjobhistory table that resides upon each SQL Server instance.

The issue is that there are idiosyncrasies with this table when it comes to storing dates and times of job executions.  I need to be able to use this information in reports and queries, but these columns are stored in a non-standard date format, which considering the fact that we're discussing system tables is baffling to me.

Solution

Before we begin let's take a look at what we're dealing with in regards to format of the run_date and run_time fields.  Though this tip pertains simply to converting the run_date to a useful format, we will be dealing with converting the run_time date in a subsequent tip.

SELECT SJ.[name], SJH.[run_date], SJH.[run_time]
FROM msdb.dbo.[sysjobhistory] SJH
INNER JOIN [msdb].dbo.[sysjobs] SJ ON SJH.[job_id] = SJ.[job_id]
WHERE SJH.[step_id] = 0 ORDER BY SJ.[name]

The above query yields the following results:

As you can see, run_date is stored in the format of YYYYMMDD.  It is stored as an integer format, not as a string as is the root format underlying the datetime data type within Microsoft SQL Server.  I suspect it will only be a matter of time before the msdb database receives the same treatment the master database did in regards to the logical migration of system tables into system catalog views, which back the Dynamic Management Views.  Ultimately, Dynamic Management Views addressing the job and backup structures along with their associated history and configurations stored in the msdb database make a perfect target for eventual expansion of the DMVs; however I digress.  Back to the matter at hand.

What is to happen if we want to use this information in any of the many system functions within SQL Server that rely on a date parameter.  I am referring to such functions as DATEADD(), DATEDIFF(), DATENAME(), or DATEPART() for instance.  Why don't we pick one and give it a shot:

SELECT SJ.[name], MAX(SJH.[run_date]) AS [last_run_date],
MAX(DATEDIFF(dd, SJH.[run_date], GETDATE())) AS [Days Since Last Run]
FROM msdb.dbo.[sysjobhistory] SJH INNER JOIN [msdb].dbo.[sysjobs] SJ ON SJH.[job_id] = SJ.[job_id]
WHERE SJH.[step_id] = 0
GROUP BY SJ.[name]
ORDER BY SJ.[name]

The above query yields the following error:

Msg 8115, LEVEL 16, State 2, Line 1
Arithmetic overflow error converting expression TO data type datetime.

You encounter an error of course, because you're attempting to pass an integer data typed parameter where a datetime parameter is expected; a classic square peg, round hole situation.  How do we address this?  A multiple-step CAST and CONVERT should do the trick.

SELECT SJ.[name], MAX(SJH.[run_date]) AS [last_run_date],
MAX(DATEDIFF(dd, CONVERT(datetime, CAST(SJH.[run_date] AS CHAR(8)), 101), GETDATE())) AS [Days Since Last Run]
FROM msdb.dbo.[sysjobhistory] SJH INNER JOIN [msdb].dbo.[sysjobs] SJ ON SJH.[job_id] = SJ.[job_id]
WHERE SJH.[step_id] = 0
GROUP BY SJ.[name]
ORDER BY SJ.[name]

Now that we see how to get this to work; lets take a moment and briefly break down the components of this conversion:

• CAST(SJH.[run_date] AS CHAR(8)) - This gets around the issue we would continue to encounter if we attempted to pass the raw integer value for the [run_date] field into the CONVERT() function.
• CONVERT(datetime, <string parameter>, 101) - The CONVERT() function can be used to modify how the standard datetime format is presented to end users in a query or report. The function expects an ending format; in this case we wish to use datetime; an initial value; and a format identifier. The format in our example converts input values to the format of MM/DD/YYYY. Please refer to Microsoft SQL Server Books Online for all available output formats.

This process can be made even easier by wrapping this code into a user-defined function.  By doing so, you'll be able to (a) minimize the amount of code necessary to accomplish the task at hand and (b) create a reusable process:

CREATE FUNCTION udf_convert_int_date (@date_in INT)  
RETURNS datetime 
AS 
BEGIN 
   DECLARE @date_out datetime 
   SET @date_out = 
CONVERT(datetime, CAST(@date_in AS CHAR(8)), 101) 

   RETURN @date_out 
END

You would then proceed to use this function as you would any other system (built-in) SQL Server function.  Below are two examples of such calls.  The first illustrates a simple call of this newly created function.  It returns the distinct results of the raw run_date field from msdb.dbo.sysjobhistory and its corresponding converted value:

SELECT DISTINCT SJH.[run_date], master.dbo.udf_convert_int_date(SJH.[run_date]) AS [converted_run_date] 
FROM msdb.dbo.[sysjobhistory] SJH

The second example is used in place of the code provided earlier in this tip.  It is used to calculate the amount of days since a SQL Server Agent Job was last run.  It utilizes the function within another function to accomplish the task at hand:

SELECT SJ.[name], MAX(SJH.[run_date]) AS [last_run_date], 
MAX(DATEDIFF(dd, master.dbo.udf_convert_int_date(SJH.[run_date]), GETDATE())) AS [Days Since Last Run] 

FROM msdb.dbo.[sysjobhistory] SJH 

INNER JOIN [msdb].dbo.[sysjobs] SJ ON SJH.[job_id] = SJ.[job_id] 

WHERE SJH.[step_id] = 0 

GROUP BY SJ.[name] 
ORDER BY SJ.[name]