SQL Server user defined function to convert MSDB integer value to time value

By:   |   Updated: 2009-05-05   |   Comments (6)   |   Related: > Functions User Defined UDF


Problem

In a recent tip I outlined a process for converting a date, stored as an integer into a datetime data type.  Date and time information for run history of SQL Server Agent jobs is stored within the msdb..sysjobshistory table as an integer data type, not as a datetime as one would expect.  Most likely for at least two reasons:

  • This structure is a legacy implementation from the earliest days of SQL Server
  • The values are stored in separate run_date and run_time columns and until SQL Server 2008 there was not a time data type per se

As promised, this tip picks up where we left off.  On converting the integer-typed run_time into a format that is more user friendly for presentation purposes.

We will still be using the same metadata repository for the SQL Server instances I administer.  From the previous tip, you may remember that 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. 

Solution

Let's take a look again at a simple query against the msdb..sysjobshistory and msdb..sysjobs tables that hold the data we're interested in:

sysJobHistory and sysJobs Data

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
GO

2009 09 01

We have already covered how to convert run_date (as integer) to a datetime data type that can then be used in date calculations such as DATEADD(), DATEDIFF(), DATENAME() or DATEPART().  The purpose of this tip is to convert the run_time value (stored as an integer data type) into a format that is more presentable for end users.  This can be accomplished in two manners, both outlined below.  The later is a standalone user-defined function (UDF) the second takes into consideration an additional UDF outlined recently in a tip on padding string values.  Reliance on this second UDF reduces the amount of code necessary.  I'll be presenting the actual query execution plan for a simple query using each process so you can determine which option is the best for your environment.

As a point of reference, the msdb..sysjobhistory.run_time values are stored as an integer, in the pattern of hhmmss.  Unfortunately for us, since this is an integer value, single digit values do not include a preceding zero (9:00 am for example is stored as 900, midnight as simply 0).

Option One - Standalone UDF

Let's take a look at our first option to address this problem which is a standalone UDF.

Option One: The Standalone UDF

CREATE FUNCTION dbo.udf_convert_int_time_1 (@time_in INT)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @time_out VARCHAR(8)
SELECT @time_out =
CASE LEN(@time_in)
WHEN 6 THEN LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 3,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
WHEN 5 THEN '0' + LEFT(CAST(@time_in AS VARCHAR(6)),1) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 2,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
WHEN 4 THEN '00' + ':' + LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
ELSE '00:00:00' --midnight
END --AS converted_time
RETURN @time_out
END
GO

The function accepts a single parameter, the integer data type time value passed to it.  Depending on the length of the parameter, the output value is formatted accordingly and the result is returned.  Revising the first query in this tip to include the results of this UDF applied to each record is presented below along with it's associated output and actual execution plan. 

Sample Execution

SELECT SJ.[name], SJH.[run_date], SJH.[run_time], 
   dbo.udf_convert_int_time_1(SJH.[run_time]) AS run_time_1 
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]
GO

2009 09 02

2009 10 02 EP

Option Two: Reliance on Padding of Input Variable

Recently, I published a tip on MSSQLTips.com on padding string values in Microsoft SQL Server.  We will be using the UDF presented in that tip to simplify the code presented above.  Afterwards we'll see what possible effect that has on performance by comparing the actual execution plan against the execution plan for the first iteration of the UDF shown above.

Option Two: Reliance on Padding of Input Variable

CREATE FUNCTION dbo.udf_convert_int_time_1 (@time_in INT)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @time_out VARCHAR(8)
SELECT @time_out =
CASE LEN(@time_in)
WHEN 6 THEN LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 3,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
WHEN 5 THEN '0' + LEFT(CAST(@time_in AS VARCHAR(6)),1) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 2,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
WHEN 4 THEN '00' + ':' + LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
ELSE '00:00:00' --midnight
END --AS converted_time
RETURN @time_out
END
GO

By utilizing the usp_pad_string() function we can eliminate the CASE code structure from the dbo.udf_convert_int_time_2 UDF.  The padding function expects four parameters: the string value to pad, the padding character, the number of instances to apply the pad, and the padding placement.  Please review the full structure of the usp_pad_string UDF in the original article.  It will pad the integer value so that further processing can be consistently applied without concern for length.  Running a comparable query to the one previously presented returns the following results and execution plan.

Sample Execution

SELECT SJ.[name], SJH.[run_date], SJH.[run_time], 
   dbo.udf_convert_int_time_2(SJH.[run_time]) AS run_time_2 
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]
GO

2009 09 03

2009 09 03 EP

The additional function call to the padding UDF has no apparent additional overhead on the execution of the query.  Which option you choose would be up to you, dependent upon your preference for UDF reliance and embedding of UDFs.  Ultimately this process is based upon conversion of time-of-day data into a presentable format.  While the CONVERT() function is capable of converting string values to presentable formats when passed datetime values, there is no functionality for time-only values.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-05-05

Comments For This Article




Monday, March 10, 2014 - 11:01:51 AM - Doug Back To Top (29687)

Option two in the article is not correct, it is still showing option 1 code: udf_convert_int_time_1

Tuesday, June 19, 2012 - 2:15:09 PM - Jeff Oliphant Back To Top (18106)

Tim, I very much appreciate this little dittie. 

 

I am a Data Architect, in IT for over 25 years, and always something new or something you just haven't had to write.   I found your 4 way solution interesting and useful. I wanted a variation on this problem (in _addition_ to the agent_datetime function ...) and found your examples which are very useful for a base modification sample. 

 

Regards, 

Jeff Oliphant, Data Architect, Atlanta GA 

Thursday, July 28, 2011 - 10:21:18 AM - Scott C Back To Top (14282)

Who says agent_datetime is efficient?  It has way too many string operations.  Besides, when you re-invent the wheel you may find there's more than one way to skin a cat (couldn't resist the mixed metafor).

Here's four new wheels for you:

SELECT TOP 100 run_date, run_time,
      -- The agent_datetime function converts every datepart into a separate string
      -- and concatenates them with punctuation
      AgentDatetime = CONVERT(DATETIME,
           CONVERT(NVARCHAR(4),run_date / 10000) + N'-' + 
           CONVERT(NVARCHAR(2),(run_date % 10000)/100)  + N'-' +
           CONVERT(NVARCHAR(2),run_date % 100) + N' ' +        
           CONVERT(NVARCHAR(2),run_time / 10000) + N':' +        
           CONVERT(NVARCHAR(2),(run_time % 10000)/100) + N':' +        
           CONVERT(NVARCHAR(2),run_time % 100), 120),
      -- Do one int-to-string conversion of run_date and run_time, then STUFF in
      -- the punctuation.  run_time is zero-padded to 6 chars.
      StuffMethod = CAST(STUFF(STUFF(CAST(run_date AS CHAR(8)),7,0,'-'),5,0,'-') + ' ' 
                   + STUFF(STUFF(RIGHT(run_time+1000000,6),5,0,':'),3,0,':') AS DATETIME),
      -- DATEADD each datepart separately, adjustments must be made
      -- for year, month, and day offsets
      DateaddMethod = DATEADD(yy, run_date / 10000 - 1900, 
                  DATEADD(m, run_date % 10000 / 100 - 1,
                  DATEADD(d, run_date % 100 - 1,
                  DATEADD(hh, run_time / 10000,
                  DATEADD(n, run_time % 10000 / 100,
                  DATEADD(s, run_time % 100, 0)))))),
      -- run_date (as a string) is a valid date representation and can be directly cast
      -- to DATETIME, then DATEADD the hour, minute, and second parts
      DateaddMethod2 = DATEADD(hh, run_time / 10000,
                  DATEADD(n, run_time % 10000 / 100,
                  DATEADD(s, run_time % 100, 
                  CAST(CAST(run_date AS CHAR(8)) AS DATETIME)))),
      -- The hour, minute, and second parts can be converted with float arithmetic
      -- The 2e-8 fudge factor avoids rounding errors
      FloatMethod = CAST(CAST(run_date AS CHAR(8)) AS DATETIME) 
                  + ((((run_time % 100) / 60.0 + (run_time % 10000 / 100)) / 60.0 
                        + (run_time / 10000)) / 24.0 + 2e-8)
FROM msdb.dbo.sysjobhistory
ORDER BY NEWID()

Tuesday, March 1, 2011 - 8:40:40 AM - Dick at Pershing Back To Top (13076)

Guys, WHY re-invent the wheel ?

there is a perfectly serviceable msdb.dbo.agent_datetime system scalar function that does all you want (date and/or time)
- efficient, already written (and debugged) by MS, and not susceptible to length issues and padding wobbles mentioned above

PLEASE check what already exists before creating an inferior solution

Wednesday, May 20, 2009 - 1:10:56 AM - komrad Back To Top (3404)

declare @i int -- hhmmss

declare @res varchar(10) -- temporary var

select @i=93054

select @i=@i+10000000 -- add zeros from left size

select @res=convert(varchar(10),@i)

select reverse(substring(reverse(@res),5,3))+':'+reverse(substring(reverse(@res),3,2))+':'+right(@res,2)

Tuesday, May 5, 2009 - 11:15:19 AM - caderoux Back To Top (3330)
SELECT time_in

,time_in / 10000 AS hours

,(time_in / 100) % 100 AS minutes

,time_in % 100 AS seconds

,DATEADD(SECOND, time_in % 100,

DATEADD(MINUTE, (time_in / 100) % 100,

DATEADD(HOUR, time_in / 10000, 0))) AS full_datetime

,CONVERT(varchar, DATEADD(SECOND, time_in % 100,

DATEADD(MINUTE, (time_in / 100) % 100,

DATEADD(HOUR, time_in / 10000, 0))), 8) AS string_datetime

FROM (

SELECT 233736 AS time_in

UNION

SELECT 93054 AS time_in

) AS sample_inputs














get free sql tips
agree to terms