Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

SQL Server function to convert integer date to datetime format

MSSQLTips author Tim Ford By:   |   Read Comments (8)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Dates

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(ddSJH.[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 8115LEVEL 16State 2Line 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(ddCONVERT(datetimeCAST(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(datetimeCAST(@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(ddmaster.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]

 

The next tip in this series will discuss conversion of the run_date column format.

Next Steps



Last Update: 3/20/2009


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, April 03, 2009 - 7:16:57 AM - rb-america Read The Tip

The function works great.  I appreciated it.

I did however notice that when I ran the T-SQL to list the jobs, their last run date, and count of days, the count of days was incorrect.  Below is a revised version, using CTE to eliminate the MAX() around your calculation of days.  This calculates correctly for me.

 Again thanks.

<p>

WITH JobHistory ( hName, hLastRunDate )

  AS ( SELECT SJ.[name]

         , MAX(SJH.[run_date]) AS [last_run_date]

         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]

)

SELECT hName , hLastRunDate

   , DATEDIFF(dd, master.dbo.udf_convert_int_date([hLastRunDate]), GETDATE()) AS [Days Since Last Run]

FROM JobHistory ORDER BY [hName]

 </p>


Friday, April 03, 2009 - 7:55:07 AM - mardukes Read The Tip

I'm sorry, but I don't know why this article was published.

1) this function, while it "works", does nothing but add the overhead of wrapping native functions in a UDF.  I'm sure it merely adds cost to the queries that use it.

2) while the nature of abstract data types (e.g. datetime) is that the interal workings are none of anyones' business, I don't think datetimes are stored as strings.  I think this conception is some sort of COBOL hangover -- again, not that it is any of my business how Microsoft does things.

3) datetime has NO format.  All formatting is done at the presentation layer.

4) the CONVERT function when used as it is here to take a string representation and make a datetime DOES NOT impart any format; the format code of 101 represents the INPUT format of the second argument.

5) what do you mean non-standard date format?  YYYYMMDD string representation of a date is ISO standard and will be interpreted implicitly by Microsoft magic!

Though I think magic is bad form:

select datediff(day, CAST(20090401 AS CHAR(8)), CAST(20090402 AS CHAR(8))) from_int

, datediff(day, '20090401', '20090402') straight_magic

 

Nice presentation though.

md


Friday, April 03, 2009 - 2:45:29 PM - aloi Read The Tip

Just wondering what is the advantage of using this udf?  I have been wondering on the advantage of using a udf vs. stored procedure.  I will do more research. 

Is it like what mardukes is saying couldn't we have done a convert()?

 


Monday, April 06, 2009 - 7:16:43 AM - mardukes Read The Tip

Functions & procedures are two different things.  Functions should return one thing.  Table functions should return one table.  Procedures are a mechanism where one can perform all sorts of tasks AND return values.  It's mostly just a matter of good form, but for good form you shouldn't think of functions and procedures as interchangible.  When doing maintenance, one should feel comfortable upon seeing a function call that nothing happened behind the scenes orther than "calculations" to map the arguments to a return value.

He SHOULD have just done convert(cast()) or convert(convert()) -- the function gained nothing, added overhead and made maintenance more difficult.


Wednesday, April 22, 2009 - 7:29:49 PM - timmer26 Read The Tip

Thank you for taking the time to comment. 

The article was witten to outline a situation where users expect a datetime value provided from a system-supplied source to behave in a manner that is consistent with datetime datatype values, and then to realize that is not possible without conversion/casting.  While your solution is completely appropriate (and it is) I would prefer to simply the process for the end user that may not understand the various formatting opportunities available and code numbers associated with the CONVERT() function.  The solution outlined will require the end users/developers/DBAs to only deal with supplying a single parameter value for the integer datetyped value; no need to remember which code parameter for the CONVERT() function corresponds to what they need.  By wrapping it in a UDF it makes it reusable.

My mantra is to make it simple for the end user, even if that means more overhead for me; it's an inverse relationship.  I also find it important that DBAs understand the inner workings of the RDBMS they support.  It makes them a stronger DBA. 

Again, thank you for your interest and we appreciate your time and comments.

 

- Tim


Friday, June 22, 2012 - 6:32:55 PM - Varun Read The Tip

How about the below query -  we can use MSDB inbuilt function datetime to convert the run date and run time to a more readable format.

 

SELECT SJ.[name], msdb.dbo.agent_datetime(SJH.[run_date], SJH.[run_time]) as [RunDateTime]

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]


Monday, January 07, 2013 - 9:30:50 PM - Michelle Read The Tip
Fantastic! I saw this when you posted it oinliralgy but didn't have the time to check then. Glad you did a recap of it as I now had the time. Records going back years is right! I had some going back to January of 2001! I had over 200k records in one of my backupset tables. It's a good thing we only use simple and daily full backups at this point. I set a limit on how far back we want to save, created a job to run the sp_delete_backuphistory. I also added a step to reindex the tables it touched as well. You might not need the backupfilegroup table, as it doesn't exist on all my servers I'm nto sure why. Probably only if you have ever performed a filegroup backup I'm not sure. These could always be werapped in an exists statement too.dbcc dbreindex ( backupfilegroup', ) ;dbcc dbreindex ( backupfile', ) ;dbcc dbreindex ( backupmediafamily', );dbcc dbreindex ( backupmediaset', );dbcc dbreindex ( backupset', );dbcc dbreindex ( restorefile', );dbcc dbreindex ( restorefilegroup', );dbcc dbreindex ( restorehistory', );Thanks Brent!

Thursday, July 18, 2013 - 3:10:39 AM - DBA Read The Tip

Very helpful and easy to understand, thanks!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.