March 24, 2014 at 2:37 pm
Hi all -
I've been tasked with writing 2 reports one showing what jobs ran between 9am and 9pm, and another between 9pm and 9am.
the problem being is Microsoft decided to store date and time as integers of the form date = 20140305 and time is in the form of 90000 for 9am.
a co-worker sent this code to concantenate and return a DATETIME.
,CONVERT(DATETIME, CONVERT(CHAR(8), h.run_date, 112) + ' '
+ STUFF(STUFF(RIGHT('000000' +
CONVERT(VARCHAR(8), h.run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
This will have to be added to the where clause and therefull executed for every row. Surely there is something better out there!!
this is what I have so far
SELECT j.name, h.message, h.run_date, h.run_time, h.step_name
FROM sysjobs j
INNER JOIN sysjobhistory h
ON j.job_id = h.job_id
where h.step_name = '(Job outcome)'
AND CONVERT(DATETIME, CONVERT(CHAR(8), h.run_date, 112) + ' '
+ STUFF(STUFF(RIGHT('000000' +
CONVERT(VARCHAR(8), h.run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
> (GETDATE() - 1)
any help is GREATLY appreciated.
March 24, 2014 at 2:54 pm
Try this:
declare @Date int = 20140314,
@Time int = 90000;
select msdb.dbo.agent_datetime(@Date,@Time);
March 24, 2014 at 3:40 pm
Brilliant!!
SELECT j.name, h.message, h.run_date, h.run_time, h.step_name
FROM sysjobs j
INNER JOIN sysjobhistory h
ON j.job_id = h.job_id
where h.step_name = '(Job outcome)'
AND msdb.dbo.agent_datetime(h.run_date,h.run_time) > (GETDATE() - 1)
is MUCH cleaner and easier to follow and returns the same set as the query with all the string/convert manipulations. I would think it would be more performant also.
thank you Mr Pettis
March 24, 2014 at 3:56 pm
Do they have similar function for run_duration ?
March 24, 2014 at 4:00 pm
Uripedes Pants (3/24/2014)
Brilliant!!
SELECT j.name, h.message, h.run_date, h.run_time, h.step_name
FROM sysjobs j
INNER JOIN sysjobhistory h
ON j.job_id = h.job_id
where h.step_name = '(Job outcome)'
AND msdb.dbo.agent_datetime(h.run_date,h.run_time) > (GETDATE() - 1)
is MUCH cleaner and easier to follow and returns the same set as the query with all the string/convert manipulations. I would think it would be more performant also.
thank you Mr Pettis
Not necessarily more performant as you are now calling a scalar function. As long as the number of rows in the dataset remains relatively small it shouldn't be an issue.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply