DATEDIFF

A general date/time function that returns the integer number of intervals of a specified type between two dates.

Synopsis

DATEDIFF(datepart,startdate,enddate)

Arguments

datepart	The name (or abbreviation) of a date or time part, specified as a quoted string. This is the unit for which the interval is measured,
startdate	The starting date/time for the interval.
enddate	The ending date/time for the interval. 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 counted within the defined date range. The date range begins at startdate and ends at enddate. (If enddate is earlier than startdate, DATEDIFF returns a negative INTEGER value.)

The datepart parameter 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	Abbreviation
year	yy
month	mm
week	wk
day	dd
hour	hh
minute	mi
second	ss
millisecond	ms

If you specify an invalid datepart value as a literal, an SQLCODE -8 error code is issued. However, if you supply an invalid datepart value as a host variable, no SQLCODE error is issued and the DATEDIFF function returns a value of NULL.

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).

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 following method call:

$SYSTEM.SQL.DATEDIFF(datepart,startdate,enddate)

Date Expression Formats

The startdate and enddate parameters can be in any of the following formats:

A Caché %Date logical value (+$H)
A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
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:

99999,99999 ($HOROLOG format)
Sybase/SQL-Server-date Sybase/SQL-Server-time
Sybase/SQL-Server-time Sybase/SQL-Server-date
Sybase/SQL-Server-date (default time is 00:00:00)
Sybase/SQL-Server-time (default date is 01/01/1900)

Sybase/SQL-Server-date is one of these five formats:

mmdelimiterdddelimiter[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]

where delimiter is a slash (/), hyphen (-), or period (.).

Sybase/SQL-Server-time represents one of these three formats:

HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}

If the year is given as two digits, the %DATE utility's sliding-date window is used (if applicable) to determine the century.

Range and Value Checking

DATEDIFF performs the following checks on input values. If a value fails a check, the null string is returned.

A valid startdate or enddate may consist of a date string (yyyy-mm-dd), a time string (hh:mm:ss), or a date and time string (yyyy-mm-dd hh:mm:ss). If both date and time are specified, both must be valid. For example, you can return a Year value if no time string is specified, but you cannot return a Year value if an invalid time string is specified.
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. For example, you cannot return a Year value if the Day value is omitted. Years must be specified as four digits. If you omit the date portion of an input value, DATEDIFF defaults to '1900–01–01'.
A time string must be properly formatted with the appropriate separator character. Because a time value can be zero, you can omit one or more time elements (either retaining or omitting the separator characters) and these elements will be returned with a value of zero. Thus, 'hh:mm:ss', 'hh:mm:', 'hh:mm', 'hh::ss', 'hh::', 'hh', and ':::' are all valid. To omit the Hour element, the date expression must not have a date portion of the string, and you must retain at least one separator character (:).
Date and time values must be within a valid range. Years: 1841 through 9999. Months: 1 through 12. Days: 1 though 31. Hours: 0 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 '02–29' is only valid if the specified year is a leap year.
Most date and time values less than 10 may include or omit a leading zero. However, an Hour value of less than 10 must include the leading zero if it is part of a datetime string. 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.

Examples

The following example returns 353 because there are 353 days ('dd') between the two timestamps:

SELECT DISTINCT DATEDIFF('dd','1999-01-01 00:00:00','1999-12-20 12:00:00')
FROM sample.person

The following example returns 31 because there are 31 years ('yy') between the two timestamps:

SELECT DISTINCT DATEDIFF('yy','1968-12-21 13:19:00','1999-12-20 00:00:00')
FROM sample.person

Note that the above examples use an abbreviation for the date part. However, you can specify the full name, as in this example:

SELECT DISTINCT DATEDIFF('year','1968-09-10 13:19:00','1999-12-20 00:00:00')
FROM sample.person