Datediff() bug

[OldCFer]

The Datediff("m",startdate,enddate) function does not return the correct value
if the startdate is
"2/28/05" and the enddate has a day value of 28,29 or 30. It will return 1
less than the correct
value. Presumably, CF is using 30 days as the month threshold, however
"2/27/05" will calculate
correctly for future dates. This is not good if you are projecting future
months for billings, deferrals etc.
It appears to be the same in 6.1 and 7, if my testing is correct.

[Tulsa]

Larry, we talked about this before and you gave me this code which will correct
the datediff bug when detected. Thought I would share it with others. Of
course it has my own values in it.

<cfset session.numbernights = datediff("d",form.dayin,form.dayout)>
<cfset Hrs = datediff("h",form.dayin,form.dayout)>
<cfif Hrs MOD 24 IS 23>
<cfset session.numbernights = session.numbernights + 1>
</cfif>

[jdeline]

The same problem is in CF 5 also. #Datediff("m","2/28/05","4/29/05")# returns 1.

[Dovie]

I've also just hit what seems to be a bug in DateDiff. my code: <cfoutput> DD
between 3/27 and 4/3 is #DateDiff('D', '3/27/2005', '4/3/2005')# is day
diff<br> DD between 3/27 and 4/4 is #DateDiff('D', '3/27/2005', '4/4/2005')# is
day diff<br> </cfoutput> Yields output: DD between 3/27 and 4/3 is 7 is day
diff DD between 3/27 and 4/4 is 7 is day diff It doesn't seem to believe
4/4/05 exists.

[OldCFer]

I was aware of the DateDiff("D", "3/27/2005", "4/4/2005") problem. It's caused by dates
spanning the daylight saving time date. Since there's only 23 hours in that day
it comes up one day short.

[Dovie]

aha! SO how does one fix said issue?

[OldCFer]

You can use:

<cfif DateDiff("h", "3/27/2005", "4/4/2005") MOD 24 is 23>
Add a day to the DateDiff("d") results
<cfelse>
Don't add a day
</cfif>

[ksmith]

There is a hotfix for datediff in CFMX 6.1. You can find it
[url]http://www.macromedia.com/go/tn_19202[/url] I believe this fix is built into CFMX
6.1 with updater1. You can check the release notes for bug 53813.

[Dovie]

Thanks all!

[OldCFer]

Another DateDiff Gotcha. If a month has 30 days or less and you do a
DateDiff("m") from the last day of the month to the corresponding day of the
next month then it returns zero. For example:
DateDiff("m","2/28/05","3/28/05") = 0
DateDiff("m","2/27/05","3/27/05") = 1
DateDiff("m","4/30/05","5/30/05") = 0
DateDiff("m","2/29/05","3/29/05") = 1

It seems like since the conversion to java there are problems with date
functions.

[TA-Selene]

That's an interesting question, is this a bug or a feature. I guess it depends
on what you are looking for.

If you think of a month as the difference between the month values of dates,
than this is a bug. However, if you think of a month as an actual full month,
it works fine. There is not a full month between 2/28/2005 and 3/28/2005
because 2/28 is the last day of the month and 3/31 is the last day of the
month.

If you are not dealing with the last day of the month (2/27 to 3/27) than
there is a month diff because they are both the same day of the month. The
logic seems a little complex, but it is consistent.

[OldCFer]

It's not consistent at all. Using that logic why does:
DateDiff("m","2/27/05","3/27/05") = 1
when all I've done is change the day from 28 to 27 in both arguments?
How can the 27th to the 27th be a month, but the 28th to the 28th isn't?

[TA-Selene]

There are two ways to look at 2/28 to 3/28.

One way is that they are one month apart because 2/27 and 3/27 are. The other
way to look at it is that 2/28 is the end of the month, so 3/28 is less than
one month away. Because 2005 is not a leap year, 2/28 is indeed the last day
of the month.

CF seems to consistently give the last day of the month this exception. I'm
not saying I agree with it, but it is consistent.

[OldCFer]

OK, July 31st is the last day of the month, right? According to you, since it's
the last day of the
month, DateDiff("m", "7/31/05","8/31/05") should give me a zero, but it gives
me 1.
DateDiff("m", "11/30/05","12/30/05"), the last day, gives me zero. If I
DateAdd("m",1,"11/30/05") it gives
me "12/30/05" why isn't the datediff() difference 1 month? "11/30/05"
represents 24 more hours
to go in the month. Why should it be treated differently than "11/29/05" and
48 more
hours? I just can't see the consistency.

[paross1]

Looks like a very obvious BUG to me! It shouldn't matter how many days are in any particular month, the system should adjust for it (and leap year, and time change, for that matter).

Phil

[TA-Selene]

Nope, I still see a consistency. The diff between 7/31 and 8/31 should be 1
because they are BOTH the last days of the month, wherease 11/30 and 12/30
sould be 0 because the end of December is 12/31. So that is consistent. And
when I say SHOULD be, I mean based on the way the function seems to work, not
because I agree with it, especially when you consider what DateAdd does. The
consistency seems to be the way that DateDiff works when the first date
supplied is the last day of the month. Again, I'm not saying I agree with the
functionality, I think it is confusing at best and complete bug at worst. I'm
just being a Devil's Advocate.

[Iceborer]

Selene, I think I get what you're saying, but how does this fit in?

DateDiff("m","4/15/05","7/15/05") = 3
DateDiff("m","4/30/05","7/30/05") = 2

Either the difference between two dates with the same "day number" is (year2 -
year1) * 12 + (month2 - month1) for ALL dates, or the function has a bug.
Using different logic at the end of a month than in the middle is incosistent.

[TA-Selene]

I think the 4/30, 7/30 difference is 2 because 4/30 is the last day of the
month while 7/31 is the end of the month.

Consider the following:

4/30 - 7/30
4/30 - 7/31

The DateDiff tag will return 2 and 3 in these cases. I think, like you do,
that there are three months difference between 4/30 and 7/30 because you get
7/30 if you add 3 months to 4/30. But what about 4/30 and 7/31? Since there
is no 4/31, I would say this should also be 3 months. However, I guess
Macromedia - or the Java engine it is running on - doesn't agree about the
difference between 4/30 and 7/30. They give us 2.

Lets look at this another way. In MX 7, the following lines both return
6/30/2005:

#DateAdd("m", 1, "5/30/2005")#
#DateAdd("m", 1, "5/31/2005")#

That's because if you add one month to 5/30, you get 6/30. Now if you add a
month to 5/31, you get 6/30 because there is no 6/31. So if Macromedia says
there is one difference for both of these, than the inverse should be true for
DateDiff. To me, that means there should be one month difference between 2/28
and 3/28, 3/29, 3/30 and 3/31.

But as we've seen, there is not the case. Only the difference between 2/28
and 3/31 reflects a month difference.

[Iceborer]

Wow, I couldn't let this one go. My guess is that the date+/- CF functions are
implemented using the Java GregorianCalendar class. End-of the-month
calculations do behave differently than mid-month calculations because of the
calendar rules. An excerpt from the docs follows.

Add rule 1. The value of field f after the call minus the value of field f
before the call is delta, modulo any overflow that has occurred in field f.
Overflow occurs when a field value exceeds its range and, as a result, the next
larger field is incremented or decremented and the field value is adjusted back
into its range.

Add rule 2. If a smaller field is expected to be invariant, but it is
impossible for it to be equal to its prior value because of changes in its
minimum or maximum after field f is changed, then its value is adjusted to be
as close as possible to its expected value. A smaller field represents a
smaller unit of time. HOUR is a smaller field than DAY_OF_MONTH. No adjustment
is made to smaller fields that are not expected to be invariant. The calendar
system determines what fields are expected to be invariant.

Example: Consider a GregorianCalendar originally set to August 31, 1999.
Calling add(Calendar.MONTH, 13) sets the calendar to September 30, 2000. Add
rule 1 sets the MONTH field to September, since adding 13 months to August
gives September of the next year. Since DAY_OF_MONTH cannot be 31 in September
in a GregorianCalendar, add rule 2 sets the DAY_OF_MONTH to 30, the closest
possible value. Although it is a smaller field, DAY_OF_WEEK is not adjusted by
rule 2, since it is expected to change when the month changes in a
GregorianCalendar.

[OldCFer]

The problem is the documentation states:
The DateDiff function determines the number of complete datepart units between
the two dates; for example, if the datepart parameter is "m" and the dates
differ by 55 days, the function returns 1.
What the hell does that mean? What is the complete datepart unit for a month?
They are inferring that some
number of days would work. Would 30 days be the magic number? If so then why
does DateDiff("m","4/30/05","7/30/05") = 2
when the difference in days is 91? Since CF is apparently using the underlying
java date calculation they
should reflect this behavior in the documentation. If it's documented it can
be dealt with. This
has a huge impact with things like revenue deferrment in accounting
applications.