Caché SQL Reference
DATEDIFF
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A date/time function that returns an integer difference for a specified datepart between two dates.
Synopsis
DATEDIFF(datepart,startdate,enddate)
Arguments
datepart The name (or abbreviation) of a date or time part. This name can be specified in uppercase or lowercase, with or without enclosing quotes. The datepart can be specified as a literal or a host variable.
startdate The starting date/time for the interval. May be a date, a time, or a datetime in a variety of standard formats.
enddate The ending date/time for the interval. May be a date, a time, or a datetime in a variety of standard formats. startdate is subtracted from enddate to determine how many datepart intervals are between the two dates.
Description
The DATEDIFF function returns the INTEGER number of the specified datepart difference between the two specified dates. The date range begins at startdate and ends at enddate. (If enddate is earlier than startdate, DATEDIFF returns a negative INTEGER value.)
DATEDIFF returns the total number of the specified unit between startdate and enddate. For example, the number of minutes between two datetime values evaluates the date component as well as the time component, and adds 1440 minutes for each day difference. DATEDIFF returns the count of the specified date part boundaries crossed between startdate and enddate. For example, that any two dates that specify sequential years (for example 2010-09-23 and 2011-01-01) return a year DATEDIFF of 1, regardless of whether the actual duration between the two dates is more than or less than 365 days. Similarly, the number of minutes between 12:23:59 and 12:24:05 is 1, although only 6 seconds actually separate the two values.
Note that DATEDIFF is provided for Sybase and Microsoft SQL Server compatibility. Similar time/date comparison operations can be performed using the TIMESTAMPDIFF ODBC scalar function.
This function can also be invoked from ObjectScript using the DATEDIFF() method call:
$SYSTEM.SQL.DATEDIFF(datepart,startdate,enddate)
Specifying an invalid datepart, startdate, or enddate to the DATEDIFF() method generates a <ZDDIF> error.
Datepart Argument
The datepart argument can be one of the following date/time components, either the full name (the Date Part column) or its abbreviation (the Abbreviation column). These datepart component names and abbreviations are not case-sensitive.
Date Part Abbreviations
year yyyy, yy
month mm, m
week wk, ww
weekday dw
day dd, d
dayofyear dy
hour hh
minute mi, n
second ss, s
millisecond ms
The weekday and dayofyear datepart values are functionally identical to the day datepart value.
DATEDIFF and TIMESTAMPDIFF do not handle quarters (3-month intervals).
If you specify a startdate and enddate that include fractional seconds, you can return the difference as a number of fractional seconds, expressed as thousands of a second (.001), as shown in the following example:
SELECT DATEDIFF('ms','62871,56670.10','62871,56670.27'),     /* returns 170 */
       DATEDIFF('ms','62871,56670.1111','62871,56670.27222') /* returns 161.12 */
 
A datepart can be specified as a quoted string or without quotes. These syntax variants perform slightly different operations:
Date Expression Formats
The startdate and enddate arguments can be in different data type formats.
The startdate and enddate arguments can be in any of the following formats:
The Caché %String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:
Sybase/SQL-Server-date is one of these five formats:
mm/dd/[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
In the first syntactic format the delimiter can be a slash (/), a hyphen (-), or a period (.).
Sybase/SQL-Server-time represents one of these three formats:
HH:MM[:SS[:FFF]][{AM|PM}]
HH:MM[:SS[.FFF]]
HH['']{AM|PM}
Years
If the year is given as two digits or the date is omitted entirely, Caché checks the sliding window to interpret the date. The system-wide default for the sliding window is 1900; thus by default a two-digit year is assumed to be in the 20th century. This is shown in the following example:
SELECT DATEDIFF('year','10/11/14','09/14/2015'),
       DATEDIFF('year','12:00:00','2004-07-09 12:00:00')
 
The sliding window default can be set system-wide or for the current process via the %DATE utility, which is documented only in the Legacy Documentation chapter in Using InterSystems Documentation. For information on establishing a sliding window for interpreting a specified date with a two-digit year, see the documentation for the ObjectScript $ZDATE, $ZDATEH, $ZDATETIME and $ZDATETIMEH functions.
Fractional Seconds
DATEDIFF returns fractional seconds as milliseconds (a three-digit integer) regardless of the number of fractional digits precision in startdate and enddate. Fractional digits beyond three are represented as fractional milliseconds. This is shown in the following example:
SELECT DATEDIFF('ms','12:00:00.1','12:00:00.2'),
       DATEDIFF('ms','12:00:00.10009','12:00:00.20007')
 
Some NLS locales specify the fractional separator as a comma (European usage) rather than as a period. If the current locale is one of these locales, DATEDIFF accepts either a period or a comma as the fractional seconds separator character for local date formats. You cannot use a comma as the fractional seconds separator for a date in $HOROLOG format, or a date in ODBC format. Attempting to do so generates an SQLCODE -8. Both of these formats require a period regardless of the current NLS locale.
Time Differences Independent of TimeFormat
DATEDIFF returns a time difference in seconds and milliseconds, even when the TimeFormat for the current process is set to not return seconds. This is shown in the following example:
  SET tfmt=##class(%SYS.NLS.Format).GetFormatItem("TimeFormat")
  DO ##class(%SYS.NLS.Format).SetFormatItem("TimeFormat",1)
     WRITE "datetime values (with seconds) are: ",!,
           $ZDATETIME("62871,56670.10",1,-1),"  ",$ZDATETIME("62871,56673.27",1,-1),!
  &sql(SELECT DATEDIFF('ss','62871,56670.10','62871,56673.27') INTO :x)
     WRITE "DATEDIFF number of seconds is: ",x,!!
  DO ##class(%SYS.NLS.Format).SetFormatItem("TimeFormat",2)
     WRITE "datetime values (without seconds) are: ",!,
           $ZDATETIME("62871,56670.10",1,-1),"  ",$ZDATETIME("62871,56673.27",1,-1),!
  &sql(SELECT DATEDIFF('ss','62871,56670.10','62871,56673.27') INTO :x)
     WRITE "DATEDIFF number of seconds is: ",x,!
  DO ##class(%SYS.NLS.Format).SetFormatItem("TimeFormat",tfmt)
 
Range and Value Checking
DATEDIFF performs the following checks on input values:
Error Handling
Examples
The following example returns 353 because there are 353 days (D) between the two timestamps:
SELECT DATEDIFF(D,'1999-01-01 00:00:00','1999-12-20 12:00:00')
 
In the following example, each DATEDIFF returns 1, because the year portion of the dates differs by 1. The actual duration between the dates is not considered:
SELECT DATEDIFF('yyyy','1910-08-21','1911-08-21') AS ExactYear,
       DATEDIFF('yyyy','1910-06-30','1911-01-01') AS HalfYear,
       DATEDIFF('yyyy','1910-01-01','1911-12-31') AS Nearly2Years,
       DATEDIFF('yyyy','1910-12-31 11:59:59','1911-01-01 00:00:00') AS NewYearSecond
 
Note that the above examples use an abbreviation for the date part. However, you can specify the full name, as in this example:
SELECT DATEDIFF('year','1968-09-10 13:19:00','1999-12-20 00:00:00')
 
The following Embedded SQL example uses host variables to perform the same DATEDIFF operation as the previous example:
  SET x="year"
  SET date1="1968-09-10 13:19:00"
  SET date2="1999-12-20 00:00:00"
  &sql(SELECT DATEDIFF(:x,:date1,:date2)
       INTO :diff)
  WRITE diff
 
The following example uses a subquery to return those records where the person date of birth is 1500 days or less from the current date:
SELECT Name,Age,DOB
FROM (SELECT Name,Age,DOB, DATEDIFF('dy',DOB,$HOROLOG) AS DaysTo FROM Sample.Person)
WHERE DaysTo <= 1500
ORDER BY Age
 
See Also