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