Why does ColdFusion DateDiff return weird / negative values?

Question

Pulling my hair again ...

I need to calculate the difference between two dates in days. I'm doing it:

<cfset d = DateDiff("d", Dateformat( active_apps.app_base_coupon_start, "dd.mm.yyyy"), Dateformat( variables.useDate, "dd.mm.yyyy") )> 

With active_apps.app_base_coupon_start = active_apps.app_base_coupon_start and variables.useDate = today = 02.10.2012 .

I reset both values, they are fine. However, dateDiff returns -168 when I searched (4 days in July, August 31, September 30 in September, October 2) 67 days.

Question:
Can someone stop me from losing my remaining hair and tell me what I'm doing wrong here, or if there is an easier way to get the difference in days?

EDIT:
Ok, it also works as follows:

 <cfif DateAdd("d", active_apps.app_grace_time, Dateformat( active_apps.app_base_coupon_start, "dd.mm.yyyy") ) GT now()> <cfdump output="e:\s\page\t\dump.txt" label="catch" var="YUP"> <cfelse> <cfdump output="e:\s\page\t\dump.txt" label="catch" var="NOPE"> </cfif> 

but I would still like to know why dateDiff returns weird values.

Answer 1

DateDiff("datepart", date1, date2) takes a date and two date objects as arguments.

DateFormat() as Adam Cameron already said returns a string, not a date object.
ColdFusion tries to read "07/27/2012" and "02/10/2012" as date objects, trying to apply some well-known date formats. That is why "02.10.2012" is interpreted as "February 10, 2012."

I would not let ColdFusion guess the date format of your string. Instead, you should create date objects using CreateDate(year, month, day) .

now() also a ColdFusion date object.

Answer 2

First of all, dateAdd() takes DATES as arguments, not dateFormat() -ed strings. dateFormat() for output, not for calculations.

You need to understand that just because “02.10.2012” looks like a date for you (and for me), this is not a date regarding the computer: this is a string.

Never use strings to calculate dates.

In your case, CF courageously tries to work out what “02.10.2012” can mean as a date, and deciding its format “mm.dd.yyyy”, which is February 10, whereas you mean Oct 2.

Answer 3

You are using an ambiguous date format. Change the DateFormat format to the international date format ( ISO 8601 ) when you do calculations by date, and everything will be a little more predictable. Note that CF does not support every ISO format option, but for the most part you just need yyyy-mm-dd , which is supported.

 <cfset d = DateDiff("d", Dateformat( active_apps.app_base_coupon_start, "yyyy-mm-dd"), Dateformat( variables.useDate, "yyyy-mm-dd") )>