Datetime difference converted to int does not return same result as datediff

Question

Datetime difference converted to int does not return same result as datediff

jelenas3 15063

SSC Veteran

Points: 241
More actions
February 26, 2014 at 12:23 am

#288652

Hi, all,
I have a situation where when calculating difference in days between 2 dates these 2 queries do not return same result.
-------------@Date1---------------
Jan 31 2014 11:59PM
-------------@Date2---------------
Jan 23 2014 12:00AM
select convert(int, @Date1- @Date2) -- returns 9
select datediff(dd,@Date2, @Date1) -- returns 8
also: select convert(float, @Date1- @Date2) -- returns 8.99999
Could you, please, explain to me from what that difference comes, how do the results get calculated in either of cases? Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

If you convert Jan 31 2014 11:59PM to int and then back to datetime, you get Feb 01 2014 12:00AM. So it is a rounding "error", resulting in a day extra.

Conclusion: do not just substract dates like integers, but use the date functions.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BrainDonor SSCoach Points: 19252 More actions · Answer 2

As an aside, the date isn't stored just as an 'integer/float equivalent' which is why you're seeing this behaviour.

A couple of years back I wrote an article for the Developers I worked with and it has this at the start:

"The DATETIME field is stored with SQL Server as two 4-byte values. The first is the number of days since 01/01/1900 and the second is the number of milliseconds since midnight (actually 1/300 of a second segments – datetime2 is the newer, more accurate version). The two together give you the data and the time."

The best resource I use for Date/Time manipulation is https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/ by Robyn Page and Phil Factor.

Steve Hall
Linkedin
Blog Site

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Koen Verbeeck (2/26/2014)
If you convert Jan 31 2014 11:59PM to int and then back to datetime, you get Feb 01 2014 12:00AM. So it is a rounding "error", resulting in a day extra.
Conclusion: do not just substract dates like integers, but use the date functions.

FYI - the rounding vs truncating behavior is described in the BOL entry for CONVERT.

That said - the two items aren't measuring the same thing, so I wouldn't necessarily go at it with a broad stroke "don't ever use subtract". Since Datediff measures unit boundaries between the two values, you might need to consider which option gives you the answer that fits your needs. If you need to know actual time elapsed (Or - you define # of days as how many increments of 24 hours have occurred rather than how many times midnight occurs between dates), the subtraction arguably gives you the better answer, just be careful about the use of CONVERT.

Example

select datediff(day, '2014-01-20 11:59:59','2014-01-21 00:00:01') --returns 1 day

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Koen Verbeeck (2/26/2014)
If you convert Jan 31 2014 11:59PM to int and then back to datetime, you get Feb 01 2014 12:00AM. So it is a rounding "error", resulting in a day extra.
Conclusion: do not just substract dates like integers, but use the date functions.

Oh, be careful now. I don't believe that's quite right especially since the INT conversion is applied to the difference of the two dates and not to the individual dates. I believe the reason why the first method produces 9 is because subtraction produces a true duration. DATEDIFF does not. DATEDIFF simply measures (in this case), the number of day borders crossed, which occurs at midnight.

As for subtracting dates, I do it all the time because it's a heck of a lot easier than trying to manage the individual components when trying to calculate the duration between two dates/times.

{EDIT} Didn't scroll down to see Matt's post on the subject. He's saying the same thing a different way. The two methods are measuring different things.

--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)

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

Jeff Moden (2/26/2014)
Koen Verbeeck (2/26/2014)
If you convert Jan 31 2014 11:59PM to int and then back to datetime, you get Feb 01 2014 12:00AM. So it is a rounding "error", resulting in a day extra.
Conclusion: do not just substract dates like integers, but use the date functions.
Oh, be careful now. I don't believe that's quite right especially since the INT conversion is applied to the difference of the two dates and not to the individual dates. I believe the reason why the first method produces 9 is because subtraction produces a true duration. DATEDIFF does not. DATEDIFF simply measures (in this case), the number of day borders crossed, which occurs at midnight.
As for subtracting dates, I do it all the time because it's a heck of a lot easier than trying to manage the individual components when trying to calculate the duration between two dates/times.
{EDIT} Didn't scroll down to see Matt's post on the subject. He's saying the same thing a different way. The two methods are measuring different things.

You're right, but since you are converting it to int, you are rounding up (the float is more correct in that perspective). 8 days and 11 hours 59 minutes is not 9 days. That's why I put "error" in double quotes. I believe the date functions are more precise in that aspect.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

Koen Verbeeck (2/26/2014)
I believe the date functions are more precise in that aspect.

I certainly agree that the date functions make life a whole lot easier in certain areas but I don't agree that they are any more accurate when it comes to durations and spans of time. Here's a simple example of displaying the difference between two dates and times in the classic HHHH:MM:SS:mmm format and the classic decimal hours format. If there's a simpler or more performant method to do the same things using only date fuctions, I'd love to see them. To be sure, there is no sarcasm or irony in that statement. I'd really like to know.

DECLARE @StartDate DATETIME

,@EndDate DATETIME

;

SELECT @StartDate = '31 Dec 2014 23:59:59.997'

,@EndDate = '01 Jan 2016 00:06:59.000'

;

--===== Subtraction method for display

SELECT CONVERT(VARCHAR(10),DATEDIFF(hh,0,@EndDate-@StartDate))

+ RIGHT(CONVERT(CHAR(12),@EndDate-@StartDate,114),10)

;

--===== Subtraction method for decimal hours

SELECT CONVERT(DECIMAL(11,1),(CONVERT(FLOAT,@EndDate-@StartDate)*24.0))

;

{EDIT} Correct spelling on the word "function".

--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)

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

Nice example Jeff, I'll keep it in mind.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP