question

siera_gld avatar image
siera_gld asked

DATEDIFF in Minutes

I have timed jobes and I am trying to measure delays in them expressed in minutes. I want to calculate it by using a hard coded HH:MM format against the actual finish time. my results seem to be expressed in at INT format though... select source, starttime, endtime, case when source = 'Step_3' then datediff(mi,datepart(n,'14:45'),datepart(n,endTime)) END as DELAY from msdb.dbo.sysdtslog90 where source in ('Step_3', 'Step_2', 'Step_1') AND CONVERT(CHAR(10), starttime, 110) BETWEEN DATEADD(d,-60, CONVERT(CHAR(10),GETDATE(),110)) AND GETDATE() and message like '%End of package execution.%' order by 3 Partial Results source starttime endtime DELAY Step_3 2011-02-07 15:14:32.000 2011-02-07 15:14:32.000 -44640 (DELAY should show 45 min (:45:xxx)
datediffdatepart
1 comment
10 |1200

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

@siera_gld Why 45 minutes? The difference between 14:45 and 15:14 (the end date value) is 29 minutes, not 45.
1 Like 1 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Two things: - You're missing a ELSE statement in your CASE. - If I understand your requirements, you want to see the time difference between 14:45 and endtime? Then I'd try something like this DATEADD(mi,datediff(mi,'14:45',endTime),'00:00')
10 |1200

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

Pavel Pawlowski avatar image
Pavel Pawlowski answered
As @Magnus Ahlkvist stated you should use something like he posted. And now why your results are wrong. In case of your example you are calculating the difference in minutes between day 46 and day 15 of year 1900. This means difference between 15. February 1900 and 15. January 1900. It is because you put result of DATEPART in the DATEDIFF function which takes DATETIME arguments. And if you put an integer as an argument, it is taken as numbers of days from '1900/01/01'. Here is as mall example and correct solution: DECLARE @startTime datetime = '2011-02-07 14:45:32.000' DECLARE @endTime datetime = '2011-02-07 15:14:32.000' --minutes from date SELECT datepart(n,@startTime) AS MinutesOfStartTime, datepart(n,@endTime) AS MinutesOfEndTIme --this caluclates difference between day no. 45 in year 1900 and 14. SELECT datediff(mi,datepart(n,@startTime),datepart(n,@endTime)) AS WrongDifference1 SELECT DATEADD(day, 45, 0) AS Day45, DATEADD(day, 14, 0) AS Day14 --this gives the same result as the first datediff SELECT DATEDIFF(mi, '1900-02-15 00:00:00.000', '1900-01-15 00:00:00.000') AS WrongDifference2 --correct statement SELECT DATEDIFF(mi, @startTime, @endTime) AS CorrectDifferenceInMinutes --correct difference in hours:minutes in case difference is less than 24 hours SELECT CAST(DATEADD(mi, DATEDIFF(mi, @startTime, @endTime), 0) as time) AS CorrectDiffHHMM
10 |1200

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

siera_gld avatar image
siera_gld answered
oops - what happens when it goes over one hour...I am not seeing correct results when it should be 93 min...it only expresses 33 min?
3 comments
10 |1200

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

Which code expressed 33 min? My snippet should give you hours and minutes. Look at this for example: select cast(DATEADD(mi,DATEDIFF(mi,'14:45','16:00'),'00:00') as time)
0 Likes 0 ·
I'm using SQL 2K5 - TIME is not appearing as datatype
0 Likes 0 ·
Ok, but still. You'll just have to convert it. Just skip the CAST, and you'll end up with **1900-01-01 01:15:00.000**
0 Likes 0 ·
john_moreno avatar image
john_moreno answered
Is this on a sql 2008 server? If so look into using cast(@starttime as time).
1 comment
10 |1200

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

@John_moreno : as said in the comment above by @siera_gld - the version is SQL2K5, and so datatype time is not there. But that's really no difference, the functions are the same to use, and then it's just a matter of presentation using convert after the value is there.
1 Like 1 ·

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.