Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Datetime difference converted to int does not return same result as datediff Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 12:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 2:49 PM
Points: 1, Visits: 5
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.
Post #1545231
Posted Wednesday, February 26, 2014 1:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 AM
Points: 12,188, Visits: 9,139
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1545248
Posted Wednesday, February 26, 2014 1:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 1,533, Visits: 7,914
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.


BrainDonor
Linkedin
Blog Site
Post #1545254
Posted Wednesday, February 26, 2014 10:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,081, Visits: 14,673
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?
Post #1545487
Posted Wednesday, February 26, 2014 9:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545687
Posted Wednesday, February 26, 2014 11:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 AM
Points: 12,188, Visits: 9,139
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1545715
Posted Thursday, February 27, 2014 6:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545826
Posted Thursday, February 27, 2014 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 AM
Points: 12,188, Visits: 9,139
Nice example Jeff, I'll keep it in mind.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1545850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse