Hello SSC,
Hope you are all having a great Monday!
I am trying to get the run_date and run_time integer values from sysjobhistory and convert them to datetime. I am running into issues. I have Googled this, but everything that I have seen either looks overly complicated or doesn't work. I am able to get the date with the function below, but is there a way to convert both columns to a single datetime value?
I can convert this to varchar and then use the substring function to manually add the ':' characters, but I am sure there is a better and faster way. I will keep this solution as a last resort.
Any assistance would be greatly appreciated and thank you all in advance!
use msdb
go
select run_date,run_time
from dbo.sysjobhistory
where step_name = 'daily updates'
order by 1 desc
DECLARE @date int, @time int;
SET @date = '20250207';
SET @time = '174500';
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(20), @date));
SELECT CONVERT(TIME, CONVERT(varchar(6), @time));
The are no problems, only solutions. --John Lennon
I don't know of a super-slick way to do this off the top of my head:
SELECT DISTINCT run_date, run_time,
CAST(CAST(run_date AS varchar(8)) + ' ' + STUFF(STUFF(RIGHT('00000' + CAST(run_time AS varchar(6)), 6), 5, 0, ':'), 3, 0, ':') AS datetime) AS run_datetime
FROM msdb.dbo.sysjobhistory
ORDER BY run_time
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 10, 2025 at 3:40 pm
Thank you, Scott!
It still looks better than most of the solutions on Google.
I haven't used STUFF in years!
Really appreciate this!
The are no problems, only solutions. --John Lennon
February 11, 2025 at 5:37 pm
You can also use the scalar function msdb.dbo.agent_datetime:
SELECT JobName = j.name,
RunDateTime = msdb.dbo.agent_datetime(jh.run_date, jh.run_time),
StepID = jh.step_id,
StepName = jh.step_name,
RunStatus = jh.run_status,
[Message] = jh.[message]
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
WHERE j.[name] = 'MyJobName'
ORDER BY msdb.dbo.agent_datetime(jh.run_date, jh.run_time)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy