ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

DATEDIFF function [Date and time]

Function 

Returns the interval between two dates.

Syntax 

DATEDIFF ( date-part, date-expression1, date-expression2 )

date-part :
year | quarter | month | week | day | hour | minute | second | millisecond

Parameters 

date-part    Specifies the date-part in which the interval is to be measured.

For more information about date-parts, see Date parts.

date-expression1    The starting date for the interval. This value is subtracted from date-expression2 to return the number of date-parts between the two arguments.

date-expression2    The ending date for the interval. Date-expression1 is subtracted from this value to return the number of date-parts between the two arguments.

Usage 

This function calculates the number of date parts between two specified dates. The result is a signed integer value equal to (date2 - date1), in date parts.

DATEDIFF results are truncated, not rounded, when the result is not an even multiple of the date part.

When you use day as the date part, DATEDIFF returns the number of midnights between the two times specified, including the second date but not the first.

When you use month as the date part, DATEDIFF returns the number of first-of-the-months between two dates, including the second date but not the first.

When you use week as the date part, DATEDIFF returns the number of Sundays between the two dates, including the second date but not the first.

For the smaller time units there are overflow values:

• milliseconds    24 days

• seconds    68 years

• minutes    4083 years

• others    No overflow limit

The function returns an overflow error if you exceed these limits.

Standards and compatibility 

• SQL/92    Transact-SQL extension.

• SQL/99    Transact-SQL extension.

• Sybase    Compatible with Adaptive Server Enterprise.

Example 

The following statement returns 1:

SELECT datediff( hour, '4:00AM', '5:50AM' )

The following statement returns 102:

SELECT datediff( month, '1987/05/02', '1995/11/15' )

The following statement returns 0:

SELECT datediff( day, '00:00', '23:59' )

The following statement returns 4:

SELECT datediff( day,
   '1999/07/19 00:00',
   '1999/07/23 23:59' )

The following statement returns 0:

SELECT datediff( month, '1999/07/19', '1999/07/23' )

The following statement returns 1:

SELECT datediff( month, '1999/07/19', '1999/08/23' )