How to convert run_date and run_time columns to datetime from SQL Agent systable

Question

Post reply

How to convert run_date and run_time columns to datetime from SQL Agent systable

Lord Slaagh

SSCertifiable

Points: 5758
More actions
February 10, 2025 at 3:19 pm
Go to Answer
#4534292
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
ScottPletcher

SSC Guru

Points: 101170
More actions
February 10, 2025 at 3:32 pm
Answer
#4534296
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".

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lord Slaagh SSCertifiable Points: 5758 More actions · Answer 1

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

SoCal_DBD Hall of Fame Points: 3387 More actions · Answer 2

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)