why does ColdFusion's DateDiff return strange/negative values?

Question

Pulling my hair out again...

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

 <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 = 27.07.2012 and variables.useDate = today = 02.10.2012.

I dumped both values, they are OK. However the dateDiff returns -168 when I was looking for (4 days in July, 31 in August, 30 in September, 2 in October) 67 days.

Question:
Can someone prevent 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 like this:

<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 is returning strange values.

Answer 1

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

DateFormat() as Adam Cameron already said returns a string and not a date object.
ColdFusion is trying to read "27.07.2012" and "02.10.2012" as date objects by trying to apply some known date formats. That's why "02.10.2012" is interpreted as "Feb 10 2012".

I wouldn't let ColdFusion guess the dateformat of your string. Instead you should create date objects by using CreateDate(year, month, day).

now() is also a ColdFusion date object.

Answer 2

First things first, dateAdd() takes DATES as arguments, not dateFormat()-ed strings. dateFormat() is for output, not for calculations.

You need to understand that just because "02.10.2012" looks like a date to you (and to me), it's not a date as far as the computer is concerned: it's a string.

Never use strings for date calculations.

In your case, CF is valiantly trying to work out what "02.10.2012" might mean as a date, and deciding it's "mm.dd.yyyy" format, which is Feb 10, whereas you mean Oct 2.