Caché SQL Reference
A date/time function that returns an integer difference for a specified datepart between two dates.
||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.
||The starting date/time for the interval. May be a date, a time, or a datetime in a variety of standard formats.
||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.
returns the total number of the specified unit between startdate
. 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
. 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 Caché ObjectScript using the DATEDIFF()
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.
If you specify a startdate
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 */
can be specified as a quoted string or without quotes. These syntax variants perform slightly different operations:
A Caché %Date logical value (+$H), also known as $HOROLOG format.
A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS.FFF), also known as ODBC format.
A Caché %String (or compatible) value.
The Caché %String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:
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 (.).
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:
returns fractional seconds as milliseconds (a three-digit integer) regardless of the number of fractional digits specified. Fractional digits beyond three are represented as fractional milliseconds. This is shown in the following example:
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
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:
WRITE "datetime values (with seconds) are: ",!,
&sql(SELECT DATEDIFF('ss','62871,56670.10','62871,56673.27') INTO :x)
WRITE "DATEDIFF number of seconds is: ",x,!!
WRITE "datetime values (without seconds) are: ",!,
&sql(SELECT DATEDIFF('ss','62871,56670.10','62871,56673.27') INTO :x)
WRITE "DATEDIFF number of seconds is: ",x,!
performs the following checks on input values:
A date string must be complete and properly formatted with the appropriate number of elements and digits for each element, and the appropriate separator character. Years must be specified as four digits. If you omit the date portion of an input value, DATEDIFF
defaults to '19000101'. An invalid date value results in an SQLCODE -8 error.
Date and time values must be within a valid range. Years: 0001 through 9999. Months: 1 through 12. Days: 1 through 31. Hours: 00 through 23. Minutes: 0 through 59. Seconds: 0 through 59. The number of days in a month must match the month and year. For example, the date '0229' is only valid if the specified year is a leap year. An invalid date value results in an SQLCODE -8 error.
Date values less than 10 (month and day) may include or omit a leading zero. Other non-canonical integer values are not permitted. Therefore, a Day value of '07' or '7' is valid, but '007', '7.0' or '7a' are not valid.
Time values may be wholly or partially omitted. If startdate
specifies an incomplete time, zeros are supplied for the unspecified parts.
An hour value less than 10 must include a leading zero. Omitting this leading zero results in an SQLCODE -8 error.
In Embedded SQL, if you specify an invalid datepart
as an input variable, an SQLCODE -8 error code is issued. If you specify an invalid datepart
as a literal, a <SYNTAX> error occurs. If you specify an invalid startdate
as either an input variable or a literal, an SQLCODE -8 error code is issued.
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 date1="1968-09-10 13:19:00"
SET date2="1999-12-20 00:00:00"
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:
FROM (SELECT Name,Age,DOB, DATEDIFF('dy',DOB,$HOROLOG) AS DaysTo FROM Sample.Person)
WHERE DaysTo <= 1500
ORDER BY Age