5

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.

Kara
  • 6,115
  • 16
  • 50
  • 57
frequent
  • 27,643
  • 59
  • 181
  • 333
  • copied your code, replaced your date vars by strings "27.07.2012" and "02.10.2012" and got 67. Do your variables contain different values or different type of values? – jan Oct 02 '12 at 14:20
  • hm. wait a sec. My dump says: `27.07.2012` and `02.10.2012` – frequent Oct 02 '12 at 14:22
  • let me check again. Must be something with the variables. – frequent Oct 02 '12 at 14:22
  • 1
    I think your date mask is mixing up month and day for 02.10.2012. Difference between 27.07.2012 and 10.02.2012 is -168 days – jan Oct 02 '12 at 14:23
  • 1
    ColdFusion will interpret `27.07.2012` as July 27, 2012 but `02.10.2012` as February 10 in all likelihood - depending I guess on your locale. – David Faber Oct 02 '12 at 14:24
  • AH. Good point! Thank you very much! Make this an answer? – frequent Oct 02 '12 at 14:27
  • @DavidFaber - `DateFormat` (and all non-LS date functions) *always* use US date conventions. Your locale does not matter. – Leigh Oct 02 '12 at 14:43
  • @all - ok. Thanks everybody. Take away are no `Dateformat` in calculations, `LS...` is my friend outside of the US and if I'm juggling integers, `createDate` first. – frequent Oct 02 '12 at 15:14
  • For what it's worth, I'll bet that a datemask of "yyyy-mm-dd" would have worked. It's still double-converting the data, though. – ale Oct 02 '12 at 16:51
  • @AlEverett - You mean with Dateformat? It does not work in all cases, because the mask is applied after the input is parsed into a date object. So the date value is already corrupted by then. – Leigh Oct 19 '12 at 19:51

3 Answers3

7

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.

Community
  • 1
  • 1
jan
  • 2,879
  • 2
  • 19
  • 28
5

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.

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
  • 1
    In addition, though CF can convert strings to dates implicitly, the standard date functions *always* use U.S. date formatting conventions ie `mm/dd/yyyy`. So `02/10/2012` will always be interpreted as February 2nd. If you need locale sensitive parsing, use the LS date functions. – Leigh Oct 02 '12 at 14:40
  • Yeah true. Never having been in USA (well: never written code there anyhow), this has never been a consideration for me. I always figure that when confronted with a round hole, don't try to bash an oval peg through it, ie: if the function wants a date, give it a date, don't give it string and force CF to convert it. – Adam Cameron Oct 02 '12 at 14:47
  • @AdamCameron Just to nitpick, ColdFusion is a (supposedly) typeless language, so string are dates if the string is formatted as such. `IsDate("2012-10-02")` will return true. – nosilleg Oct 02 '12 at 14:53
  • @AdamCameron: ok `DateFormat`. So I need not worry when adding two dates that they are potentially different. I'm redoing a page and I*m only getting stuff like `02` for months and `2012` for years and then need to add first/last day and compare this date to other dates... Boy I wish I just had a paper calendar to cut stuff out from... – frequent Oct 02 '12 at 15:00
  • Adam - Agreed, I think it is almost always better to use date objects. Less ambiguity and room for problems. But I have worked with legacy apps that use all strings, which only worked because they were all "mm/dd/yyyy". – Leigh Oct 02 '12 at 15:02
  • @frequent - If you are receiving month and year number, simply use `createDate` to generate a valid date object. – Leigh Oct 02 '12 at 15:05
  • @frequent, as @jan notes below, you'll want to use the `createDate()` function instead of the `dateFormat()` function in your call to `dateDiff()`. – David Faber Oct 02 '12 at 15:05
  • @nosilleg: there's a difference between "will automatically be cast to a date" and "can be interpretted as a date", and "*is* a date". NB: CF is *loosely typed*, not *typeless*. Run this: d = now(); writeOutput(d.getClass().getName()); It's definitely got a type. – Adam Cameron Oct 02 '12 at 15:27
  • @AdamCameron hence the _(supposedly)_ in my comment. Type doesn't matter in most places, but will 500 in others. And in the above example if `active_apps.app_grace_time` is the string "28 Jan 2012" I pity the person who doesn't use `DateFormat`. – nosilleg Oct 02 '12 at 15:36
1

You're using an ambiguous date format. Change the DateFormat to international date format (ISO 8601) whenever you make date calculations and things will be a bit more predictable. Note that CF doesn't support every variant of the ISO format, 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") )>
nosilleg
  • 2,143
  • 1
  • 22
  • 36
  • Agreed about non-ambiguous formats. But you do not want to use `DateFormat`. That just converts the string into a date - and back into a string again. Then `DateDiff` converts it back into a date. Better to pass in a date *object* to begin with. – Leigh Oct 02 '12 at 14:54
  • @Leigh I disagree, especially in cases when you're starting with a string to begin with. Parsing it yourself in order to disambiguate it doesn't make sense when one of the key features of the language means it's taken care of for you. Is `active_apps.app_base_coupon_start` a string, a date object a ODBC date, etc? Who cares, `DateFormat` to the rescue. – nosilleg Oct 02 '12 at 15:09
  • Agree with @Leigh, disagree with @nosilleg. If you want to use a function to transform a string to a date, use `parseDateTime()`, not `dateFormat()`. Round hole, round peg. – Adam Cameron Oct 02 '12 at 15:31
  • @AdamCameron If you're wanting to do timespan comparisons from the beginning of one day to the end of another, you are again making more work for yourself if you don't use `DateFormat`. – nosilleg Oct 02 '12 at 15:43
  • @nosilleg - No, `ParseDateTime` is for parsing. `DateFormat` is for converting a *date object* into a user friendly string. If you are just passing string into `DateDiff` anyway, there is no need to use `DateFormat` because `DateDiff` does the same conversion, except in one (1) step instead of three (3). But in this specific case the result will still be wrong because `02.10.2012` is still interpreted as February. The fact that this question came up at all is a great example of why it is better to use date objects instead of strings :) They do not suffer from this kind of ambiguity. – Leigh Oct 02 '12 at 15:48
  • @Leigh Apparently you wrote your comment before my last one, but it's showing after mine for me. Anyway, check my response to AdamCameron for a reason to use `DateFormat`. And functions can be used however a developer chooses. I can't find anything in the documentation that says `DateFormat` is only to be used in the situations you stipulated. `DateFormat` is for converting a date string/object into a date string of a given format. Any definition more strict than that has bias. – nosilleg Oct 02 '12 at 16:00
  • @nosilleg - I think you are missing the point - which is accuracy not personal preference. Date strings are ambiguous and lead to problems exactly like this one. Try your code with the original **dd.mm.yyyy** string value ie `02.10.2012`. The answer will be wrong. – Leigh Oct 02 '12 at 16:29
  • @Leigh Which is why my answer says to use a non-ambiguous format. Not everything starts as an object and hand tuning a string into an object just so you can work in objects is a waste of time when the language has features that will handle things for you automatically. – nosilleg Oct 02 '12 at 17:39
  • @nosilleg - I suspect you did not try your code with a dd.mm.yyyy value as I suggested, or you would see the problem .. ;-) – Leigh Oct 02 '12 at 18:00
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/17463/discussion-between-nosilleg-and-leigh) – nosilleg Oct 02 '12 at 19:13