question

MichKarlsson avatar image
MichKarlsson asked

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.

datetimecalculated-column
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
WRBI avatar image
WRBI answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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