View comments | RSS feed
CFML Reference Previous Up One Level Next
ColdFusion Functions


DateDiff

Description

Determines the number of units by which date1 is less than date2.

Return value

A number of units, of type datepart.

Category

Date and time functions

Syntax 

DateDiff("datepart", "date1", "date2")

See also

DateAdd, DatePart, CreateTimeSpan

History

New in ColdFusion MX: This function now calculates negative date differences correctly; its output may be different from that in earlier releases.

Parameters

Parameter Description
datepart
 String:
  • yyyy: Year
  • q: Quarter
  • m: Month
  • y: Day of year
  • d: Day
  • w: Weekday
  • ww: Week
  • h: Hour
  • n: Minute
  • s: Second
date1
 Date/time object, in the range 100 AD-9999 AD. See "How ColdFusion processes two-digit year values".
date2
 Date/time object, in the range 100 AD-9999 AD. See "How ColdFusion processes two-digit year values".

Usage

To find the number of days between date1 and date2, use DayofYear or Day.

When datepart is Weekday, DateDiff returns the number of weeks between the dates. If date1 falls on a Monday, DateDiff counts the number of Mondays to date2. It counts date2 but not date1.

If interval is Week, however, DateDiff returns the number of calendar weeks between the dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; it does not count date1, even if it falls on a Sunday.

If date1 refers to a later date than date2, DateDiff returns a negative number.

When passing date/time object as a string, enclose it in quotation marks. Otherwise, it is interpreted as a numeric representation of a date/time object.

Example 

<!--- This example shows the use of DateDiff --->
<cfif IsDefined("FORM.date1") and IsDefined("FORM.date2")>
  <cfif IsDate(FORM.date1) and IsDate(FORM.date2)>
    <p>This example uses DateDiff to determine the difference
    in   <cfswitch expression = #type#>
      <cfcase value = "yyyy">years</cfcase>
      <cfcase value = "q">quarters</cfcase>
      <cfcase value = "m">months</cfcase>
      <cfcase value = "y">days of year</cfcase>  
      <cfcase value = "d">days</cfcase>  
      <cfcase value = "w">weekdays</cfcase>  
      <cfcase value = "ww">weeks</cfcase>  
      <cfcase value = "h">hours</cfcase>  
      <cfcase value = "n">minutes</cfcase>  
      <cfcase value = "s">seconds</cfcase>    
      <CFDEFAULTCASE>years</CFDEFAULTCASE></cfswitch>
       dateparts between date1 and date2.
    <cfif DateCompare(FORM.date1, FORM.date2) is not 0>
    <p>The difference is <cfoutput>#Abs(DateDiff
     (type, FORM.date2, FORM.date1))#</cfoutput>
    <cfswitch expression = #type#>
      <cfcase value = "yyyy">years</cfcase>
      <cfcase value = "q">quarters</cfcase>
      <cfcase value = "m">months</cfcase>
      <cfcase value = "y">days of year</cfcase>  
      <cfcase value = "d">days</cfcase>  
      <cfcase value = "w">weekdays</cfcase>  
      <cfcase value = "ww">weeks</cfcase>  
      <cfcase value = "h">hours</cfcase>  
      <cfcase value = "n">minutes</cfcase>  
      <cfcase value = "s">seconds</cfcase>    
      <CFDEFAULTCASE>years</CFDEFAULTCASE></cfswitch>.
    <cfelse>
...
Previous Up Next

ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6

Comments are no longer accepted for ColdFusion MX. ColdFusion 8 is the current version.

Comments


daveline said on Dec 12, 2002 at 8:58 PM :
this function has changed since coldfusion 5
the result of
#dateDiff("m","1/1/2002","12/1/2002")# was 11
in MX it is 10

clu@oncallinteractiv said on Jan 7, 2003 at 7:42 PM :
I think it's buggin in CFMX. Try this on a MX machine:

#DateDiff('m','1/1/1999','3/1/1999')# = 1
#DateDiff('m','1/1/1999','3/2/1999')# = 1
#DateDiff('m','1/1/1999','3/3/1999')# = 2
#DateDiff('m','1/1/1999','3/31/1999')# = 2

clu@oncallinteractiv said on Jan 10, 2003 at 6:28 PM :
Just FYI: This bug is fixed in update pack 1.
Hutz said on Feb 9, 2004 at 7:56 AM :
DateDiff applies daylight savings differences to Day Of Year ("y") and Day ("d") calculations, and this leads to non-intuitive results. Viz:

(The first date of the datediff is during daylight savings time, the second date is not; The first datediff uses 10:30pm and 0:00, the second datediff uses 11:30pm and 0:00)
#DateDiff("d", CreateDate(2003, 10, 25), CreateDateTime(2004, 2, 6, 22, 24, 0))#
#DateDiff("d", CreateDate(2003, 10, 25), CreateDateTime(2004, 2, 6, 23, 30, 0))#

The first one returns 104, the second 105.

Move the date so BOTH are NOT during daylight savings:

#DateDiff("d", CreateDate(2003, 10, 27), CreateDateTime(2004, 2, 6, 22, 24, 0))#
#DateDiff("d", CreateDate(2003, 10, 27), CreateDateTime(2004, 2, 6, 23, 30, 0))#

And they both return the same 102.

To further perplex the issue, the intuitive way to make the above first result happen would be to convert the local datetime to UTC (11:30 daylight savings is really 12:30 the next day, so when converting to UTC, it would make sense for there to be a difference between 10:30pm and 11:30 pm). HOWEVER, when you do this, instead what happens is that there is no longer any difference in the output between daylight-savings days and non-daylight-savings days:

#DateDiff("y", DateConvert("Local2utc",CreateDate(2003, 10, 25)), DateConvert("Local2utc",CreateDateTime(2004, 2, 6, 22, 24, 0)))#
#DateDiff("y", DateConvert("Local2utc",CreateDate(2003, 10, 25)), DateConvert("Local2utc",CreateDateTime(2004, 2, 6, 23, 30, 0)))#

returns 105 for both, and:

#DateDiff("y", DateConvert("Local2utc",CreateDate(2003, 10, 27)), DateConvert("Local2utc",CreateDateTime(2004, 2, 6, 22, 24, 0)))#
#DateDiff("y", DateConvert("Local2utc",CreateDate(2003, 10, 27)), DateConvert("Local2utc",CreateDateTime(2004, 2, 6, 23, 30, 0)))#

returns 102 for both.
WatVduck said on Apr 13, 2004 at 3:25 AM :
I am trying to use datedif to calc age.
DATEDIF(yyyy,14-Apr-1996,13-Apr-2004) = 8
whereas in every other language i use it gives 7 years.
Why is this?
jrunrandy said on Apr 13, 2004 at 7:49 AM :
Are you running ColdFusion MX or ColdFusion MX 6.1? AFAIK, DateDiff works OK in CFMX. For 6.1, there is a hot fix to the DateDiff function: http://www.macromedia.com/support/coldfusion/ts/documents/duplicate_hotfix.htm
No screen name said on Aug 3, 2004 at 3:53 AM :
this hotfix didn't work on my mx 6.1
godardk said on Sep 2, 2004 at 12:03 PM :
the patch worked for me, CFMX Standard 6.1 on win2000
captainplanet said on Mar 8, 2005 at 6:44 AM :
<cfdump var="#DateDiff("d", createdate(2005,4,1), createdate(2005,5,1))#"> returns 29

<cfdump var="#DateDiff("d", createdate(2005,4,2), createdate(2005,5,2))#"> returns 29


<cfdump var="#DateDiff("d", createdate(2005,4,3), createdate(2005,5,3))#"> returns 29

<cfdump var="#DateDiff("d", createdate(2005,4,4), createdate(2005,5,4))#"> returns 30

I think this is a problem. We're writing finacial software & the interest calculation is off because of this problem. I need some help.......
captainplanet said on Mar 8, 2005 at 6:45 AM :
Also, we are fully updated with all the patches.
jrunrandy said on Mar 15, 2005 at 1:40 PM :
captainplanet: Are you ColdFusion MX or ColdFusion MX 6.1? My understanding is that the DateDiff problem is fixed in the CFMX 6.1 updater: http://www.macromedia.com/support/documentation/en/coldfusion/mx61updater/releasenotes_cfmx61_updater02.html
No screen name said on Aug 9, 2005 at 12:13 PM :
It seems to me that DateDiff returns the larger of 2 options when asked to calculate over the fall daylight savings. The following code returns 75, for the number of minutes between 00:45 and 01:00 on Oct 30, 2005. But 15 would also be a correct answer since 01:00 occurs twice on this day. And my code is expecting the 15. Is there anyway to specifiy this difference?

DateDiff ("n",CreateDateTime("2005","10","30","0","45","0"),CreateDateTime("2005","10","30","1","0","0"))

always returns 75. What about the other correct answer, 15?
No screen name said on Aug 26, 2005 at 9:59 AM :
I'm running CFMX7 - it seems that DateDiff('d', start, end) doesn't count the number of day boundries crossed (as it does in SQL, etc). For instance:

DateDiff('d', '8/26/2005 8:00pm', '8/27/2005 9am') returns 0 - instead of 1 (as I would expect). Are my expectations wrong?
No screen name said on Aug 26, 2005 at 10:09 AM :
OK - further research has found that apparently datediff('d'....) counts "whole days" and not date boundaries. This is stupid if you ask me - if I wanted to count the notion of "whole days" - I'd do 24 hours.

Anyway, a fix I found is to format the days to drop the time. For instance, if I'm counting the number of days a certain date is from today:
DateDiff('d', DateFormat(variables.mydate, 'mm/dd/yyyy'), now()) would yield 0 for today, 1 for yesterday, etc. Notice I only do the format on the first date since it basically makes it count as 12am on that day - so anytime today would count as at least a whole day.

Macromedia: could you please fix this or add a new date part that would count day boundaries?

 

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/6/CFML_Reference/functions-pt158.htm