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)
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')
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
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?
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)
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**
Is this on a sql 2008 server? If so look into using cast(@starttime as time).
@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.
