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 12»»

Adding YYYYMMDD and HHMMSS to get mm/dd/yyyy HH:MM:SS Expand / Collapse
Author
Message
Posted Thursday, June 12, 2014 2:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:30 PM
Points: 76, Visits: 290
I am writing some reporting queries to show execution start and end times of my SQL jobs. I need to show execution start and end times for each of these jobs in "MM/DD/YYYY HH:MM:SS" format.

SysJobHistory has data in integer format (YYYYMMDD for date; HHMMSS for time; execution time as integer respectively in run_date, run_time, run_duration columns).
What is the efficient way to do this. For now, I am doing it in a very crude way as below:

select	sjh.Job_ID, CONVERT(varchar(20), CONVERT(date, CONVERT(varchar(8), sjh.run_date), 112),110) run_date, 
CONVERT(date, CONVERT(varchar(8), sjh.run_date), 112) run_date2,
DATEADD ( -- Add execution seconds to the start time after converting YYYYMMDD HHMMSS to MM/DD/YYYY HH:MM:SS format
ss,
sjh.Run_Duration,
CAST (
SUBSTRING (CAST (run_date as VARCHAR), 5, 2) + '/' + -- MM
SUBSTRING (CAST (run_date as VARCHAR), 7, 2) + '/' + -- DD
SUBSTRING (CAST (run_date as VARCHAR), 1, 4) + ' ' + -- YYYY
SUBSTRING (REPLICATE ('0', 6-LEN(CAST (Run_Time AS VARCHAR))) + CAST (Run_Time AS VARCHAR), 1, 2) + ':' + -- HH (add a 0 at the beginning to account for single digit hours)
SUBSTRING (CAST (Run_Time AS VARCHAR), 3, 2) + ':' + -- MM
SUBSTRING (CAST (Run_Time AS VARCHAR), 5, 2) -- SS
AS DATETIME)
) AS NewRunTime,
sjh.run_time, sjh.run_duration, sjh.run_status
from msdb..sysjobhistory sjh -- ON j.ExternalJobID = sjh.Job_id
where sjh.run_date = CONVERT (VARCHAR (8), GetDate(), 112) and sjh.step_id = 0 and run_status in (0, 1)

Your inputs will be appreciated.

SQLCurious
Post #1580268
Posted Thursday, June 12, 2014 3:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
How about this (sorry, but I didn't work with your query):


select
*,
cast(cast(run_date as varchar) + ' ' + stuff(stuff(right('000000' + cast(run_time as varchar),6),5,0,':'),3,0,':') as datetime)
from
msdb.dbo.sysjobhistory;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1580284
Posted Thursday, June 12, 2014 3:52 PM This worked for the OP Answer marked as solution


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 @ 3:37 PM
Points: 3,374, Visits: 7,300
There's no need to make that extra work with the date. You need to work with the run_duration column because it comes in format HHMMSS as well.
select	sjh.Job_ID, 
run_date,
CONVERT(varchar(20), CONVERT(date, CONVERT(varchar(8), sjh.run_date), 112),110) run_date,
CONVERT(date, CONVERT(varchar(8), sjh.run_date), 112) run_date2,
DATEADD ( -- Add execution seconds to the start time after converting YYYYMMDD HHMMSS to MM/DD/YYYY HH:MM:SS format
ss,
((sjh.Run_Duration / 100) * 60) + (sjh.Run_Duration % 100),
CAST( run_date AS char(8)) + ' '
+ STUFF( STUFF( RIGHT( '00000' + CAST( sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
) AS NewRunTime,
sjh.run_duration,
sjh.run_status
from msdb..sysjobhistory sjh -- ON j.ExternalJobID = sjh.Job_id
where sjh.run_date = CONVERT (VARCHAR (8), GetDate(), 112)
and sjh.step_id = 0
and run_status in (0, 1)

Note that I don't expect jobs that run for one hour or more.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1580286
Posted Thursday, June 12, 2014 4:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:30 PM
Points: 76, Visits: 290
Thank you guys! I appreciate that.

SC
Post #1580292
Posted Thursday, June 12, 2014 4:55 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
Modified the dateadd just in case you have jobs that run an hour or more:


select
sjh.job_id,
CONVERT(varchar(20), CAST(CAST(sjh.run_date as char(8)) as date),110) run_date,
CAST(CAST(sjh.run_date as char(8)) as date) run_date2,
DATEADD ( -- Add execution seconds to the start time after converting YYYYMMDD HHMMSS to MM/DD/YYYY HH:MM:SS format
ss,
((((sjh.run_duration / 10000) * 60) + (sjh.run_duration / 100) % 100) * 60) + (sjh.run_duration % 100),
CAST(CAST(sjh.run_date as char(8)) + ' ' + STUFF(STUFF(right('000000' + CAST(sjh.run_time as char(6)),6),5,0,':'),3,0,':') as datetime)
) NewRunTime,
sjh.run_time,
sjh.run_duration,
sjh.run_status
from
msdb..sysjobhistory sjh -- ON j.ExternalJobID = sjh.Job_id
where
sjh.run_date = CONVERT(VARCHAR(8), GetDate(), 112) and
sjh.step_id = 0 and
run_status in (0, 1)


I had to look closer at Luis' code as the outer stuff confused me at first until I realized he was stuffing them in left to right and I did it right to left.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1580301
Posted Sunday, June 15, 2014 12:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:35 AM
Points: 1,345, Visits: 3,816
Didn't see Lynn's code before I wrote the example, almost the same but without a string thingy



/*Converting INT date and time to datetime */
DECLARE @INT_YYYYMMDD INT = 20140704;
DECLARE @INT_HHMMSS INT = 012056;
SELECT DATEADD(SECOND,
(((@INT_HHMMSS / 10000) * 3600) -- hours to seconds
+ (((@INT_HHMMSS / 100) % 100) * 60) -- minutes to seconds
+ (@INT_HHMMSS % 100)) -- seconds
, CONVERT(DATETIME2(0),CAST(@INT_YYYYMMDD AS VARCHAR(8)),112))

Results
2014-07-04 01:20:56
Post #1580913
Posted Sunday, June 15, 2014 9:55 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
Eirikur Eiriksson (6/15/2014)
Didn't see Lynn's code before I wrote the example, almost the same but without a string thingy



/*Converting INT date and time to datetime */
DECLARE @INT_YYYYMMDD INT = 20140704;
DECLARE @INT_HHMMSS INT = 012056;
SELECT DATEADD(SECOND,
(((@INT_HHMMSS / 10000) * 3600) -- hours to seconds
+ (((@INT_HHMMSS / 100) % 100) * 60) -- minutes to seconds
+ (@INT_HHMMSS % 100)) -- seconds
, CONVERT(DATETIME2(0),CAST(@INT_YYYYMMDD AS VARCHAR(8)),112))

Results
2014-07-04 01:20:56


Just curious... since the use of CONVERT kills the possibility of portability anyway (and I don't believe in portable code for anything but C.R.U.D.), why did you feel it necessary to use DATETIME2? Definitely not a challenge here... just curious.


--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 #1580940
Posted Sunday, June 15, 2014 1:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:35 AM
Points: 1,345, Visits: 3,816
Jeff Moden (6/15/2014)
Eirikur Eiriksson (6/15/2014)
Didn't see Lynn's code before I wrote the example, almost the same but without a string thingy



/*Converting INT date and time to datetime */
DECLARE @INT_YYYYMMDD INT = 20140704;
DECLARE @INT_HHMMSS INT = 012056;
SELECT DATEADD(SECOND,
(((@INT_HHMMSS / 10000) * 3600) -- hours to seconds
+ (((@INT_HHMMSS / 100) % 100) * 60) -- minutes to seconds
+ (@INT_HHMMSS % 100)) -- seconds
, CONVERT(DATETIME2(0),CAST(@INT_YYYYMMDD AS VARCHAR(8)),112))

Results
2014-07-04 01:20:56


Just curious... since the use of CONVERT kills the possibility of portability anyway (and I don't believe in portable code for anything but C.R.U.D.), why did you feel it necessary to use DATETIME2? Definitely not a challenge here... just curious.


Just being slightly stingy here saving two bytes


DECLARE @DT DATETIME      = GETDATE();
DECLARE @DT2 DATETIME2(0) = GETDATE();

SELECT 'DATETIME' AS DATA_TYPE, DATALENGTH(@DT) AS DATA_LENGTH
UNION ALL
SELECT 'DATETIME2(0)', DATALENGTH(@DT2);

Results
DATA_TYPE    DATA_LENGTH
------------ -----------
DATETIME 8
DATETIME2(0) 6


And of course I have been careful not to do funny stuff with datetime since someone told me off a while back
Post #1580952
Posted Sunday, June 15, 2014 2:40 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
Eirikur Eiriksson (6/15/2014)

And of course I have been careful not to do funny stuff with datetime since someone told me off a while back


Gosh, I hope that wasn't me. I prefer DATETIME over the other datatypes because you can subtact a start date from and end date to get the duration in one easy step (sans final formatting).


--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 #1580960
Posted Sunday, June 15, 2014 3:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:35 AM
Points: 1,345, Visits: 3,816
Jeff Moden (6/15/2014)
Eirikur Eiriksson (6/15/2014)

And of course I have been careful not to do funny stuff with datetime since someone told me off a while back


Gosh, I hope that wasn't me. I prefer DATETIME over the other datatypes because you can subtract a start date from and end date to get the duration in one easy step (sans final formatting).


Cannot remember who it was, the name sounded like Just Married, who ever that is
Post #1580965
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse