How to calculate NEW End_DateTime column from Start_DateTime plus duration

Question

question

MichKarlsson asked Jun 1, '18

How to calculate NEW End_DateTime column from Start_DateTime plus duration

SQL Agent Jobs; In msdb you have dbo.sysjobhistory who gives you Run_date and Run_time as type INT which easily can convert to Start_Datetime with dbo.agent_datetime. I get: 2018-05-17 01:13:09.240. Run_duration is INT as well. I use STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') as 'Time_HH:MM:SS'. I get 27:31:20

I want to calculate and to have a new column as End_datetime. (i.e Start_datetime + Run_duration) My jobs are running more than 24h etc so date can go inte next day as well.

Where to start? After conversion or start with the INT colummns Run_date, Run_time, Run_duration?

Simpliest script would be most helpful.

datetime calculated-column

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-06-01T09:07:16Z

WRBI answered Jun 1, '18

Hi @MichKarlsson,

Have you visited the MSDN Page for sysjobhistory? In the comments section at the bottom there is a discussion about this topic and times going over 24 hours.

The following is taken from those comments and is untested by myself.

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.agent_datetime(@date int, @time int)
RETURNS DATETIME
AS
BEGIN
RETURN
  (
    CONVERT(DATETIME,
          CONVERT(NVARCHAR(4),CASE WHEN @time > 235959 THEN CONVERT(VARCHAR(10), DATEADD(DAY, @time/240000, CONVERT(CHAR(10), @date, 120)), 112) ELSE @date END / 10000) + N'-' + 
          CONVERT(NVARCHAR(2),(CASE WHEN @time > 235959 THEN CONVERT(VARCHAR(10), DATEADD(DAY, @time/240000, CONVERT(CHAR(10), @date, 120)), 112) ELSE @date END % 10000)/100)  + N'-' +
          CONVERT(NVARCHAR(2),CASE WHEN @time > 235959 THEN CONVERT(VARCHAR(10), DATEADD(DAY, @time/240000, CONVERT(CHAR(10), @date, 120)), 112) ELSE @date END % 100) + N' ' +        
          CONVERT(NVARCHAR(2),CASE WHEN @time > 235959 THEN @time - (@time/240000) * 240000 ELSE @time END / 10000) + N':' +        
          CONVERT(NVARCHAR(2),(CASE WHEN @time > 235959 THEN @time - (@time/240000) * 240000 ELSE @time END % 10000)/100) + N':' +        
          CONVERT(NVARCHAR(2),CASE WHEN @time > 235959 THEN @time - (@time/240000) * 240000 ELSE @time END % 100),
    120)
  )
END
GO

Hope it helps/leads you to a solution.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question