November 3, 2008

Caveat using dateDiff() to compare difference in days

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;

	date1 = now();
	date2 = myQuery.dateCreated;
	dateDifference = dateDiff("d", date1, date2);	

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:

	date1 = createDate(year(now()), month(now()), day(now()));

Or using dateFormat():

	date1 = dateFormat(now(), "yyyy/mmm/dd");

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!

© Michael Sharman 2017