DateDiff and Wholeness

This post is more than 2 years old.

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>

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>

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, 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

Archived Comments

Comment 1 by Robert Zehnder posted on 7/1/2011 at 8:43 PM

Yeah, I probably shouldn't publicly admit this, but generally I abs() the difference to make sure it is a positive number, especially if I am just checking the difference in days and don't care if it is days before or days after.

Comment 2 by Sharon posted on 7/1/2011 at 9:15 PM

Seeing as the topic is dates and unexpected behavior, I've gotten caught in the "#isDate(dateVar)#" function because CF is *very* forgiving about what constitutes a date. CF liked it, but the date still needed to be separately validated and formatted for anything outside of CF.

Comment 3 by PaulH posted on 7/1/2011 at 9:57 PM

since cf datetimes are decimal days, you could also do something like int(d1)-int(d2).

Comment 4 by Phillip Senn posted on 7/1/2011 at 10:18 PM

In Microsoft SQL Server, select DateDiff(d,'6/21/2011 18:09:06','6/22/2011')
yields 1.
Also notice that the d is not inside quotation marks because the syntax for SQL Server is different from ColdFusion.

Comment 5 by Richard Hefter posted on 7/1/2011 at 10:57 PM

As the goofball who recommended this topic to Ray, I just want to clarify. No mater what date part you are subtracting, DateDiff uses the time. So 1/22/2011 2pm to 1/22/2012 1pm is 0 years, 11 months etc. My solution was to use a Dateformat on the "from date" which can eliminate the time. It's not technically a date object but ColdFusion is very kind with it's typeless nature and treats it like one.

Ray: FYI. 12 midnight is the beginning of a new day (happy new year at 12 midnight).

Comment 6 by Raymond Camden posted on 7/1/2011 at 10:59 PM

Ah yeah - that does make sense. So the end of the day is 11:59:59:999 PM. :)

Comment 7 by Grumpy CFer posted on 7/6/2011 at 2:17 PM

I think it's generally accepted that 2011-06-06 24:00:00 is the exact same moment as 2011-06-07 00:00:00. People tend not to use 24, tho.

http://en.wikipedia.org/wik... has a mention of midnight.

I also DateFormat to get rid of times instead of CreateDate.

Comment 8 by gfbj posted on 6/3/2015 at 2:16 PM

I needed to use a date no later than today. This works for me: <cfif 10*(tenddate-now())="" gt="" 1="" gt="" 0=""><cfset tenddate="dateFormat(now()," "mm="" dd="" yyyy")=""></cfif>