Jiansen Lu
SQL Server UDF to convert integer date to datetime format
March 31st 2009 13:31
SQL Server UDF 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:
name run_date run_time
Back Up Database-iDBA 20090318 233736
Back Up Database-MSSQLTips 20080110 95240
Back Up Database-MSSQLTips 20080110 173536
Back Up Database-MSSQLTips 20080110 180133
Back Up Database-Northwind 20090318 233724
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]
name last_run_date Days Since Last Run
Back Up Database-iDBA 20090318 0
Back Up Database-MSSQLTips 20080110 433
Back Up Database-Northwind 20090318 0
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
run_date converted_run_date
20080110 2008-01-10 00:00:00.000
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]
name last_run_date Days Since Last Run
Back Up Database-iDBA 20090318 0
Back Up Database-MSSQLTips 20080110 433
Back Up Database-Northwind 20090318 0
The next tip in this series will discuss conversion of the run_date column 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:
name run_date run_time
Back Up Database-iDBA 20090318 233736
Back Up Database-MSSQLTips 20080110 95240
Back Up Database-MSSQLTips 20080110 173536
Back Up Database-MSSQLTips 20080110 180133
Back Up Database-Northwind 20090318 233724
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]
name last_run_date Days Since Last Run
Back Up Database-iDBA 20090318 0
Back Up Database-MSSQLTips 20080110 433
Back Up Database-Northwind 20090318 0
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
run_date converted_run_date
20080110 2008-01-10 00:00:00.000
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]
name last_run_date Days Since Last Run
Back Up Database-iDBA 20090318 0
Back Up Database-MSSQLTips 20080110 433
Back Up Database-Northwind 20090318 0
The next tip in this series will discuss conversion of the run_date column format.
| 51 |
| Vote |
Add To: 
subscribe to this blog
