Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

HHMMSS int field to human-friendly time? Expand / Collapse
Author
Message
Posted Friday, January 6, 2012 1:02 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 1, 2014 7:26 AM
Points: 908, Visits: 2,804
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.)
Post #1231697
Posted Friday, January 6, 2012 1:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1231734
Posted Friday, January 6, 2012 2:44 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 3,135, Visits: 11,481
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.



Post #1231750
Posted Sunday, January 8, 2012 5:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 11,194, Visits: 11,141
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1232054
Posted Sunday, January 8, 2012 10:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1232111
Posted Sunday, January 8, 2012 10:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems


  Post Attachments 
Integer DateTime Race 01.gif (265 views, 8.69 KB)
Post #1232118
Posted Sunday, January 8, 2012 10:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1232119
Posted Sunday, January 8, 2012 7:16 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 3,135, Visits: 11,481
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






Post #1232177
Posted Sunday, January 8, 2012 7:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1232179
Posted Sunday, January 8, 2012 9:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 11,194, Visits: 11,141
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1232187
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse