June 12, 2014 at 2:38 pm
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:
selectsjh.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
frommsdb..sysjobhistory sjh -- ON j.ExternalJobID = sjh.Job_id
wheresjh.run_date = CONVERT (VARCHAR (8), GetDate(), 112) and sjh.step_id = 0 and run_status in (0, 1)
Your inputs will be appreciated.
SQLCurious
June 12, 2014 at 3:45 pm
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;
June 12, 2014 at 3:52 pm
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.
selectsjh.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
frommsdb..sysjobhistory sjh -- ON j.ExternalJobID = sjh.Job_id
wheresjh.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.
June 12, 2014 at 4:13 pm
Thank you guys! I appreciate that.
SC
June 12, 2014 at 4:55 pm
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.
June 15, 2014 at 12:43 am
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
June 15, 2014 at 9:55 am
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
Change is inevitable... Change for the better is not.
June 15, 2014 at 1:11 pm
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:56Just 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 😀
June 15, 2014 at 2:40 pm
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
Change is inevitable... Change for the better is not.
June 15, 2014 at 3:13 pm
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:hehe:
😎
June 16, 2014 at 8:46 am
Have you got a link that you could PM me? I need to take a look at it to see what you're talking about.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply