I had some problems a little while ago using dateCompare() and the same thing bit me recently with dateDiff(). Well really I had a problem with createODBCDate(), but let me explain.
I wanted to compare the “day” value between 2 dates like;
<cfscript>
date1 = now();
date2 = myQuery.dateCreated;
dateDifference = dateDiff("d", date1, date2);
</cfscript>
The initial problem was that “myQuery.dateCreated” has a time value associated with it, as does the value of now(). This meant that where I should have had (or expected to have) a value of “1″ if date2 was the next day, I wasn’t because the times were being calculated and both dates where within a 24-hour range. Makes sense.
My first simple fix (which was what I tried before) was to add a createODBCDate() around my dates.
This doesn’t work though, indeed when you ouput the results of createODBCDate() it looks as though you have a date object with no time but you really DO have a time…you just can’t see it 
2 ways around this both involve stripping out the time part…using createDate() which is a bit unwieldy:
<cfscript> date1 = createDate(year(now()), month(now()), day(now())); </cfscript>
Or using dateFormat():
<cfscript> date1 = dateFormat(now(), "yyyy/mmm/dd"); </cfscript>
So another note to self…whenever you want to do any sort of date comparisons watch out for the “time” values unless of course you want the times as part of your comparison!
2 Comments
I’ve found the best way to do what you’re trying to do is this:
diff = Fix(date1) – Fix(date2)
ColdFusion date/times are basically decimal numbers; the integer part is the date, the decimal part is the time. So the above code would give you a difference of 1 day even if the two dates were only separated by a few minutes either side of midnight.
I think I originally picked up this tip from Ben Nadel…
That’s a good practice no matter where you are: it’s common to want only the date part (or only the time part, in some cases), and for the most part, grabbing only the piece you need will work.
You do want to be careful with those operations, just as you would with regular numbers. fix(date) will give you the date on which a certain event occurred, and fix(date1) – fix(date2) will tell you the difference on the calendar between two events, but if you need to know the number of full days that have elapsed between events, you should use fix (date1 – date2).
Example: if date1 is November 3, 2008 3:00 AM, and date 2 is November 1, 2008 6:00 PM (1 day 18 hours), fix(date1) – fix(date2) = 2, and fix(date1 – date2) = 1.