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?