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

• Lord Slaagh

  SSCertifiable

  Points: 5766

  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: 101192

  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".

• Lord Slaagh

  SSCertifiable

  Points: 5766

  More actions

  February 10, 2025 at 3:40 pm

  #4534311

  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: 3389

  More actions

  February 11, 2025 at 5:37 pm

  #4535281

  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)