DateDiff and Wholeness

This surprised a reader of mine recently and while it falls into the “Obvious” category, I bet it’s something folks forget - or may not remember when debugging a problem with their application. Without running the code below, can you tell me what the result is?

<cfset today2 = "2011-06-21 16:09:06"> <cfset testdiff = datediff("d", today2, "06/22/2011") >

<cfoutput> The result of the diff is : #testdiff# </cfoutput> </code>

If you answered 1, congratulations, you’re wrong. Your answer makes sense. The first date is July 21st and the second date is July 22nd. But notice the time value in the first block. That’s 4:09 PM. The second date doesn’t have a time so it defaults to midnight (or one ms after midnight - I always get confused by that - is midnight the end of the previous day or the start of the new one?)

The right answer is 0 and the reason why is made clear from the docs (emphasis mine):

The DateDiff function determines the number of complete datepart units between the two dates;

In the example above, a complete day had not passed between the two values, therefore 0 was returned. So what do you do if you want a “practical” or “What humans expect” answer? Drop the times. Here’s one way - and there are probably about 10 other ways as well:

<cfset d1 = "2011-06-21 18:09:06"> <cfset d2 = "2011-06-22 16:20:00">

<cfset d1 = createDate(year(d1), month(d1), day(d1))> <cfset d2 = createDate(year(d2), month(d2), day(d2))> <cfset testdiff = datediff(“d”, d1, d2) >

<cfoutput> The result of the diff is : #testdiff# </cfoutput> </code>

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Comments