HHMMSS int field to human-friendly time?

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  January 6, 2012 at 1:02 pm

  #250873

  I'm looking at the next_run_time field in sysjobschedules, which is stored as an integer with a presumed format of HHMMSS according to BOL. I'd like to get this to a human-friendly time display of HH:MM:SS. I have code that's working, but it's ugly. Really ugly. I know there's better available but either my search skills are seriously lacking or nobody's sharing and I'm drawing a blank. Since it's an int field, there has to be a way of left padding with 0.

  Here's what I have:

  SELECT next_run_date ,

  next_run_time ,

  LEFT(RIGHT('000000' + CAST(next_run_time AS VARCHAR(6)), 6),

  2) + ':' + SUBSTRING(RIGHT('000000'

  + CAST(next_run_time AS VARCHAR(6)),

  6), 3, 2) + ':'

  + RIGHT(RIGHT('000000' + CAST(next_run_time AS VARCHAR(6)),

  6), 2) AS TheTime

  FROM msdb.dbo.sysjobschedules AS s

  (BTW, this is going into an SSRS report so if there's a way to format it on that end I'm all for it.)

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  January 6, 2012 at 1:59 pm

  #1429869

  I searched on google for "sql server convert sysjobschedules to datetime" and the very first hit has the following function.

  create FUNCTION [dbo].[udfGetDateTimeFromInteger]

  (

  @intDate int,

  @intTime int

  )

  RETURNS datetime

  AS BEGIN

  -- Declare the return variable here

  DECLARE @DT_datetime datetime = NULL,

  @str_date varchar(11),

  @str_time varchar(8)

  if(@intDate is not null and @intDate > 0)

  begin

  select @str_date = CONVERT(varchar(11),@intDate)

  select @str_date = SUBSTRING(@str_date,1,4)+'/'+SUBSTRING(@str_date,5,2)+'/'+SUBSTRING(@str_date,7,2)

  if @intTime=0

  select @str_time ='000000'

  else

  select @str_time = right('0'+CONVERT(varchar(11),@intTime),6)

  select @str_time = SUBSTRING(@str_time,1,2)+':'+SUBSTRING(@str_time,3,2)+':'+SUBSTRING(@str_time,5,2)

  select @DT_datetime = CAST(@str_date+' '+@str_time as datetime)

  end

  -- Return the result of the function

  RETURN @DT_datetime

  END

  Then to use it to get your time only i did this.

  SELECT next_run_date ,

  next_run_time ,

  convert(varchar, dbo.udfGetDateTimeFromInteger(next_run_date, next_run_time), 108)

  FROM msdb.dbo.sysjobschedules AS s

  You can either use this function of roll it into your select (which might be a bit tricky). Your method does also work and is probably bit quicker, although unless you have a ton of sql jobs performance really isn't going to be much of a factor here.

  --edit-- fat fingers strike again. 😛

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 6, 2012 at 2:44 pm

  #1429883

  Here is a solution that doesn't use conversion to character string and back again.

  select

  next_run_date ,

  next_run_time ,

  NEXT_RUN_DATETIME =

  -- convert date

  dateadd(dd,((next_run_date)%100)-1,

  dateadd(mm,((next_run_date)/100%100)-1,

  dateadd(yy,(nullif(next_run_date,0)/10000)-1900,0)))+

  -- convert time

  dateadd(ss,next_run_time%100,

  dateadd(mi,(next_run_time/100)%100,

  --dateadd(hh,nullif(next_run_time,0)/10000,0)))

  -- Fix for prior line, because a time of 0 is valid

  dateadd(hh,@Time/10000,0)))

  from

  msdb.dbo.sysjobschedules AS s

  Results:

  next_run_date next_run_time NEXT_RUN_DATETIME

  ------------- ------------- -----------------------

  20120109 131501 2012-01-09 13:15:01.000

  20120107 113020 2012-01-07 11:30:20.000

  20120109 105000 2012-01-09 10:50:00.000

  20120107 20000 2012-01-07 02:00:00.000

  20120107 51007 2012-01-07 05:10:07.000

  20111221 200000 2011-12-21 20:00:00.000

  20120106 170100 2012-01-06 17:01:00.000

  20111221 163000 2011-12-21 16:30:00.000

  20120110 34000 2012-01-10 03:40:00.000

  0 0 NULL

  20120106 181500 2012-01-06 18:15:00.000

  20120107 500 2012-01-07 00:05:00.000

  20120109 20010 2012-01-09 02:00:10.000

  20120106 170200 2012-01-06 17:02:00.000

  20120106 170000 2012-01-06 17:00:00.000

  0 0 NULL

  20120107 110007 2012-01-07 11:00:07.000

  ...

  ...

  Edit to post bug fix.

• Paul White

  SSC Guru

  Points: 150467

  More actions

  January 8, 2012 at 5:55 am

  #1430143

  There's a system function shipped as part of msdb that turns an integer date and an integer time into a DATETIME. Being undocumented, using it directly isn't be supported (and it might be changed or removed in future with no deprecation cycle). It is unchanged in the latest SQL Server 2012 preview though. Anyway, it's called dbo.agent_datetime, and has the following definition:

  CREATE FUNCTION agent_datetime(@date int, @time int)

  RETURNS DATETIME

  AS

  BEGIN

  RETURN

  (

  CONVERT(DATETIME,

  CONVERT(NVARCHAR(4),@date / 10000) + N'-' +

  CONVERT(NVARCHAR(2),(@date % 10000)/100) + N'-' +

  CONVERT(NVARCHAR(2),@date % 100) + N' ' +

  CONVERT(NVARCHAR(2),@time / 10000) + N':' +

  CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +

  CONVERT(NVARCHAR(2),@time % 100),

  120)

  )

  END

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  January 8, 2012 at 10:16 am

  #1430194

  Sean Lange (1/6/2012)

  ---

  I searched on google for "sql server convert sysjobschedules to datetime" and the very first hit has the following function.

  Unfortunately, the function fails for certain date time combinations...

  select dbo.udfGetDateTimeFromInteger(20121224,2819)

  Msg 242, Level 16, State 3, Line 1

  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  January 8, 2012 at 10:54 am

  #1430200

  To add to the reasons not to use undocumented or, sometimes, even documented MS provided code...

  The Developers of SQL Server will occasionally make significant performance mistakes in their code just like the rest of us.

  I absolutely agree that the msdb.dbo.sysjobschedules will never have enough rows in it to become a real performance concern. I am, however, concerned that someone may copy the code from the msdb.dbo.agent_datetime() function that MS provided and use it for much larger things. If someone were to use that function for converting million row inputs multiple times per day, you could be stressing your server just because of the way the function was built. To summarize and as Michael suggested, conversions to character based datatypes can take a real toll on performance.

  Of course, such statements on performance of code aren't worth a hoot without a little evidence. 🙂

  Here's the typical million row test table...

  --=====================================================================================================================

  -- Create and populate the test table.

  -- Nothing in this section is a part of the solutions being tested.

  -- We're just building the test data here.

  --=====================================================================================================================

  --===== Conditionally drop the test table to make reruns easier in SSMS

  IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

  DROP TABLE #TestTable

  ;

  --===== Create and populate the test table on the fly

  WITH

  cteGenDates AS

  (

  SELECT TOP (1000000)

  SomeDateTime = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2012','2013')+CAST('2012' AS DATETIME)

  FROM sys.all_columns ac1,

  sys.all_columns ac2

  )

  SELECT next_run_date = CAST(CONVERT(CHAR(8),SomeDateTime,112) AS INT),

  next_run_time = CAST(REPLACE(CONVERT(CHAR(8),SomeDateTime,108),':','') AS INT)

  INTO #TestTable

  FROM cteGenDates

  ;

  GO

  And here's the test harness. You need to setup SQL Profiler to measure this one because SET STATISTICS TIME ON really and unfairly slows the MS code down a lot!

  --=====================================================================================================================

  -- Do the test using the solutions found so far for converting Integer-based Dates and Times to DATETIME values.

  -- To take display times out of the picture, all results are dumped to a "bit-bucket" variable.

  -- RUN THIS TEST WITH SQL PROFILER RUNNING TO SEE THE PERFORMANCE DIFFERENCES.

  -- Don't use SET STATISTICS TIME ON for this test because it really makes the MS code suffer.

  --=====================================================================================================================

  GO

  --===== Michael's Solution ============================================================================================

  --===== Declare the "bit-bucket" variable

  DECLARE @Bitbucket DATETIME;

  --===== Run the test

  select

  @Bitbucket =

  -- convert date

  dateadd(dd,((next_run_date)%100)-1,

  dateadd(mm,((next_run_date)/100%100)-1,

  dateadd(yy,(nullif(next_run_date,0)/10000)-1900,0)))+

  -- convert time

  dateadd(ss,next_run_time%100,

  dateadd(mi,(next_run_time/100)%100,

  dateadd(hh,nullif(next_run_time,0)/10000,0)))

  from

  #TestTable

  ;

  GO

  --===== msdb.dbo.agent_datetime Function ==============================================================================

  --===== Declare the "bit-bucket" variable

  DECLARE @Bitbucket DATETIME;

  --===== Run the test

  SELECT @Bitbucket = msdb.dbo.agent_datetime(next_run_date,next_run_time)

  FROM #TestTable

  ;

  GO

  Here are the results on my 9 year old, single cpu war-horse...

  

  Heh... Michael must be slipping... his code is "only" 13 times faster. 😛

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  January 8, 2012 at 10:56 am

  #1430201

  As a sidebar, it looks like they've really messed up the colorization on the code windows again. I sent an email to the SSC webmaster and have gotten no response. I'll send one to Steve.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 8, 2012 at 7:16 pm

  #1430257

  Jeff Moden (1/8/2012)

  ---

  To add to the reasons not to use undocumented or, sometimes, even documented MS provided code...

  The Developers of SQL Server will occasionally make significant performance mistakes in their code just like the rest of us.

  I absolutely agree that the msdb.dbo.sysjobschedules will never have enough rows in it to become a real performance concern. I am, however, concerned that someone may copy the code from the msdb.dbo.agent_datetime() function that MS provided and use it for much larger things. If someone were to use that function for converting million row inputs multiple times per day, you could be stressing your server just because of the way the function was built. To summarize and as Michael suggested, conversions to character based datatypes can take a real toll on performance.

  Of course, such statements on performance of code aren't worth a hoot without a little evidence. 🙂

  Here's the typical million row test table...

  Heh... Michael must be slipping... his code is "only" 13 times faster. 😛

  I hate to rain on results that show my code to be faster, but I think a fairer test would be if you had the function contents "in-line" or had my code in a function. The overhead of the function call can have a large impact on the results.

  Demo Performance Penalty of User Defined Functions

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78601

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  January 8, 2012 at 7:31 pm

  #1430259

  Michael Valentine Jones (1/8/2012)

  ---

  The overhead of the function call can have a large impact on the results.

  Understood and agreed... that's precisely the reason I posted such a test... to show just how bad using a MS provided scalar function can be when compared to simple in-line code. Speaking of which, if you'd like to convert your code to an in-line Table Valued Function, I'd be happy to include that in the testing. Unless I'm terribly mistaken, you won't see much of a difference using such an iTVF.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Paul White

  SSC Guru

  Points: 150467

  More actions

  January 8, 2012 at 9:19 pm

  #1430265

  Jeff Moden (1/8/2012)

  ---

  To add to the reasons not to use undocumented or, sometimes, even documented MS provided code...the developers of SQL Server will occasionally make significant performance mistakes in their code just like the rest of us.

  That's extremely generous of you Jeff. My impression is that the SQL code behind SSMS and in some of the system databases like msdb, must be written by a completely different team: the SQL code is most often pretty shonky, if we're honest. This isn't a particularly bad example, but it could/should have been written as an in-line function:

  CREATE FUNCTION dbo.agent_datetime_inline

  (

  @Date integer,

  @Time integer

  )

  RETURNS TABLE WITH SCHEMABINDING

  AS RETURN

  SELECT

  CONVERT(datetime,

  CONVERT(nvarchar(4), @Date/ 10000) + N'-' +

  CONVERT(nvarchar(2),(@Date % 10000)/100) + N'-' +

  CONVERT(nvarchar(2), @Date % 100) + N' ' +

  CONVERT(nvarchar(2), @Time / 10000) + N':' +

  CONVERT(nvarchar(2),(@Time % 10000)/100) + N':' +

  CONVERT(nvarchar(2), @Time % 100),

  120) AS date_time

  GO

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Paul White

  SSC Guru

  Points: 150467

  More actions

  January 8, 2012 at 9:24 pm

  #1430266

  Jeff Moden (1/8/2012)

  ---

  Michael Valentine Jones (1/8/2012)

  ---

  The overhead of the function call can have a large impact on the results.

  Understood and agreed... that's precisely the reason I posted such a test... to show just how bad using a MS provided scalar function can be when compared to simple in-line code. Speaking of which, if you'd like to convert your code to an in-line Table Valued Function, I'd be happy to include that in the testing. Unless I'm terribly mistaken, you won't see much of a difference using such an iTVF.

  I think Michael was proposing that the test could be made fairer by:

• Converting the MS function to in-line; or
• Converting Michael's code to a scalar function

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi