DATEDIFF Function [Date and Time]

Returns the interval between two dates.

Syntax

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

Parameters

Parameters
Parameter	Description
date-part	Specifies the date part in which the interval is to be measured.
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.

Returns

INT

Examples

The following statement returns 1:

SELECT DATEDIFF( HOUR, '4:00AM', '5:50AM' )
FROM iq_dummy

The following statement returns 102:

SELECT DATEDIFF( MONTH, '1987/05/02', '1995/11/15' )
FROM iq_dummy

The following statement returns 0:

SELECT DATEDIFF( DAY, '00:00', '23:59' ) FROM iq_dummy

The following statement returns 4:

SELECT DATEDIFF( DAY, '1999/07/19 00:00', '1999/07/23
23:59' ) FROM iq_dummy

The following statement returns 0:

SELECT DATEDIFF( MONTH, '1999/07/19', '1999/07/23' )
FROM iq_dummy

The following statement returns 1:

SELECT DATEDIFF( MONTH, '1999/07/19', '1999/08/23' )
FROM iq_dummy

The following statement returns 4:

SELECT DATEDIFF(MCS, '2009-11-03 11:10:42.033185',
'2009-11-03 11:10:42.033189') FROM iq_dummy

The following statement returns 15:

SELECT DATEDIFF(MICROSECOND, '2009-11-10
14:57:52.722001', '2009-11-10 14:57:52.722016')
FROM iq_dummy

The following statement returns 1,500,000:

SELECT DATEDIFF(MCS, '2000/07/07/07 07:07:06.277777',
'2000/07/07/07 07:07:07.777777') FROM iq_dummy

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. For example, the following statement returns the value 5. Midnight of the first day 2003/08/03 is not included in the result. Midnight of the second day is included, even though the time specified is before midnight.

SELECT DATEDIFF( DAY, '2003/08/03 14:00', '2003/08/08 14:00' ) FROM iq_dummy

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. For example, both of the following statements return the value 9:

SELECT DATEDIFF( MONTH, '2003/02/01', '2003/11/15' ) FROM iq_dummy;
SELECT DATEDIFF( MONTH, '2003/02/01', '2003/11/01' ) FROM iq_dummy;

The first date 2003/02/01 is a first-of-month, but is not included in the result of either query. The second date 2003/11/01 in the second query is also a first-of-month and is included in the result.

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 example, in the month 2003/08, the dates of the Sundays are 03, 10, 17, 24, and 31. The following query returns the value 4:

SELECT DATEDIFF( week, '2003/08/03', '2003/08/31' ) FROM iq_dummy;

The first Sunday (2003/08/03) is not included in the result.

Standards and Compatibility

SQL—Transact-SQL extension to ISO/ANSI SQL grammar.
Sybase—Compatible with Adaptive Server Enterprise.