datediff producing erroneous result when datetime is between midnight and 1AM

Question

datediff producing erroneous result when datetime is between midnight and 1AM

polkadot

SSChampion

Points: 13664
More actions
October 15, 2013 at 11:34 am

#278857

Needed a way to find datediff which renders result as follows:
-- HH:MI:SS:MMM(24H) format .
ie. 17:59:30:000 <---I WANT THIS FORMAT
instead of 17.9917 (which has to be read 17 hours and 99/60 minutes).
found this link and a SOLUTION:
http://www.mindfiresolutions.com/Get-Time-Difference-in-Hours-Minutes-Seconds-and-milliseconds-in-SQL-Server-2276.php
CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms, ActualStartTime, EndTime), 0), 114) AS ELAPSED
WORKS WELL EXCEPT when start time is just after midnight
--CORRECT
CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms,'2013-09-13 17:49:36.000', '2013-09-14 12:40:47.000'), 0), 114) AS ELAPSED
renders 18:51:11:000<----CORRECT
--INCORRECT
CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000'), 0), 114) AS ELAPSED
This renders 01:42:46:000 <----INCORRECT (should be 25:42:46) AS IN 25 hours, 42 minutes, 46 seconds.
How to handle for start times that are between midnight and 01:00?
--Quote me
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 1

Hrm... I might be missing something here, but why is the second case incorrect? September this year had 30 days, so the difference between 22 after midnight and 4 after 2 in the morning the next day should indeed be about 1:42, as October 1st is the next day after September 30th. Am I not seeing something here?

- 😀

Nevyn SSChampion Points: 14210 More actions · Answer 2

The convert you are using is only showing the hours, minutes and seconds, and the answer you want is 1 day, 1 hour, 42 minutes, and 46 seconds

You can try something like

select convert(varchar,DATEDIFF(mi,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000')/60) + right(CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000'), 0), 114),10)

to get hours over 24 in the hours count.

You can also break it down yourself and avoid using date/string manipulation by using division and remainders, and converting the answers into strings

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

You could use a function to return the three values Hours, Minutes, Seconds for the time difference.

How you then display them is a simple matter of formatting in the front end

create function TimeDifference(@Date1 datetime,@Date2 datetime)

returns table

with schemabinding

as

return

select

datediff(hour,@Date1,@Date2) as [hours]

, datediff(minute,@Date1,@Date2)%60 as [minutes]

, datediff(second,@Date1,@Date2)%60 as [seconds]

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ScottPletcher SSC Guru Points: 100942 More actions · Answer 4

You'll need to format the hours separately, as below, since style "114" will only handle up to 23 hours:

SELECT

CAST(DATEDIFF(ms, ActualStartTime, EndTime) / 3600000 AS varchar(3)) +

RIGHT(CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms, ActualStartTime, EndTime), 0), 114), 10) AS ELAPSED

FROM (

select cast('2013-09-13 17:49:36.000' as datetime) AS ActualStartTime,

cast('2013-09-14 12:40:47.000' as datetime) AS EndTime

union all

select '2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000'

) AS test_data

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

Similar to Scott's last:

SELECT CAST(DATEDIFF(ms,StartDT, EndDT)/3600000 AS VARCHAR) + ':' +

RIGHT(CAST(CAST(DATEADD(ms, DATEDIFF(ms,StartDT, EndDT)%86400000, 0) AS TIME) AS VARCHAR(12)), 9)

FROM

(

SELECT StartDT='2013-09-30 00:22:05.000', EndDT='2013-10-01 02:04:51.000'

) a;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

Nevyn (10/15/2013)
The convert you are using is only showing the hours, minutes and seconds, and the answer you want is 1 day, 1 hour, 42 minutes, and 46 seconds
You can try something like
select convert(varchar,DATEDIFF(mi,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000')/60) + right(CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000'), 0), 114),10)
to get hours over 24 in the hours count.
You can also break it down yourself and avoid using date/string manipulation by using division and remainders, and converting the answers into strings

~~Look again, please. There is absolutely no way that there are more than 25 hours between 10:05PM one day and 02:04AM the very next day.~~

My apologies... I really misread that one! That's 00:22:05 and not 22:05 like I read it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 7

2013-09-30 00:22:05.000

Jeff Moden (10/15/2013)
Look again, please. There is absolutely no way that there are more than 25 hours between 10:05PM one day and 02:04AM the very next day.

Looks like 22 minutes after midnight to me not 10:05PM. 🙂

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

dwain.c (10/15/2013)
2013-09-30 00:22:05.000
Jeff Moden (10/15/2013)
Look again, please. There is absolutely no way that there are more than 25 hours between 10:05PM one day and 02:04AM the very next day.
Looks like 22 minutes after midnight to me not 10:05PM. 🙂

Ah crud. You're right. My apologies. I'd like to blame it on old eyes but can't on this one..

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

Shifting gears back to the original subject... IF you're using the DATETIME datatype, you can really cheat on this one. As a bit of a sidebar, there's great utility in being able to do direct addition and subtraction of dates and times. I'll never understand why MS didn't include such a simple capability with the DATE and TIME datatypes. If they ever change that for the DATETIME datatype, there will be some high velocity porkchops in some designer's future. 😛

Details are in the code below and I've created a test table in case folks want to do performance testing or "effective range" testing. Solutions with a DATEDIFF in milliseconds have their limits.

--===== Create a million row test table of start and end dates

-- where the end date is always later than the start date.

-- In this case, there could be a full century of time between the dates.

IF OBJECT_ID('tempdb..#JBMTest','U') IS NOT NULL DROP TABLE #JBMTest;

WITH cteRandomStartDT AS

(

SELECT TOP 1000000

StartDT = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME)

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

)

SELECT StartDT

,EndDT = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2100') + StartDT

INTO #JBMTest

FROM cteRandomStartDT

;

--===== Calculate the Delta-T for each start/end date pair in the table.

-- I'm dumping the output to a variable to take display times out of the picture.

-- The formula calculates the total difference in time, converts that to HH:MI:SS:mmm format,

-- and then stuffs the first 2 characters out and replaces them with difference in time

-- measured in whole hours.

DECLARE @Bitbucket VARCHAR(20);

SET STATISTICS TIME ON;

SELECT @Bitbucket = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))

FROM #JBMTest;

SET STATISTICS TIME OFF;

GO

--===== Just to show that it really does work...

SELECT *

,DeltaT = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))

FROM #JBMTest

ORDER BY EndDT-StartDT

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

SELECT STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))

+1 to Jeff for that! Absolutely brilliant.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

dwain.c (10/15/2013)
SELECT STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))
+1 to Jeff for that! Absolutely brilliant.

Heh... thanks Dwain but, nah... not brilliant. :blush: Just lazy. I hate typing long formulas... I make too many typing mistakes when they get long. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)