datediff

Calculates the number of date parts between two specified dates or times.

Syntax

datediff(datepart, {date, date | time, time | bigtime, bigtime | datetime, datetime | bigdatetime, bigdatetime}])

Parameters

datepart – is a date part or abbreviation. For a list of the date parts and abbreviations recognized by the SAP ASE server, see Transact-SQL Users Guide.
date expression1 – is an expression of type datetime, smalldatetime, bigdatetime, bigtime, date, time, or a character string in a datetime format.
date expression2 – is an expression of type datetime, smalldatetime, bigdatetime, bigtime, date, time, or a character string in a datetime format.

Examples

Example 1 – Returns the number of microseconds between two bigdatetimes:

declare @a bigdatetime
declare @b bigdatetime
select @a = "apr 1, 1999  00:00:00.000000"
select @b = "apr 2, 1999  00:00:00.000000"
select datediff(us, @a, @b)
--------------------------
86400000000

Example 2 – Returns the overflow size of milliseconds return value:

select datediff(ms, convert(bigdatetime, "4/1/1753"), convert(bigdatetime, "4/1/9999"))
Msg 535, Level 16, State 0:
Line 2:
Difference of two datetime fields caused overflow at runtime.
Command has been aborted

Example 3 – Finds the number of days that have elapsed between pubdate and the current date (obtained with the getdate function):
```
select newdate = datediff(day, pubdate, getdate()) 
    from titles 
```

Example 4 – Finds the number of hours between two times:

declare @a time
declare @b time
select @a = "20:43:22"
select @b = "10:43:22"
select datediff(hh, @a, @b)
-----------
   -10

Example 5 – Finds the number of hours between two dates:

declare @a date
declare @b date
select @a = "apr 1, 1999"
select @b = "apr 2, 1999"
select datediff(hh, @a, @b)
-----------
     24

Example 6 – Finds the number of days between two times:

declare @a time
declare @b time
select @a = "20:43:22"
select @b = "10:43:22"
select datediff(dd, @a, @b)
-----------
     0

Example 7 – Returns the overflow size of milliseconds return value:

select datediff(ms, convert(date, "4/1/1753"), convert(date, "4/1/9999"))
Msg 535, Level 16, State 0:
Line 2:
Difference of two datetime fields caused overflow at runtime.
Command has been aborted

Usage

datediff takes three arguments. The first is a datepart. The second and third are chronological values. For dates, times, datetimes and bigdatetimes, the result is a signed integer value equal to date2 and date1, in date parts.
- If the second or third argument is a date, and the datepart is an hour, minute, second, millisecond, or microsecond, the dates are treated as midnight.
- If the second or third argument is a time, and the datepart is a year, month, or day, then zero is returned.
- datediff results are truncated, not rounded when the result is not an even multiple of the datepart.
- For the smaller time units, there are overflow values and the function returns an overflow error if you exceed these limits.
datediff produces results of datatype int, and causes errors if the result is greater than 2,147,483,647. For milliseconds, this is approximately 24 days, 20:31.846 hours. For seconds, this is 68 years, 19 days, 3:14:07 hours.
datediff results are always truncated, not rounded, when the result is not an even multiple of the date part. For example, using hour as the date part, the difference between “4:00AM” and “5:50AM” is 1.

When you use day as the date part, datediff counts the number of midnights between the two times specified. For example, the difference between January 1, 1992, 23:00 and January 2, 1992, 01:00 is 1; the difference between January 1, 1992 00:00 and January 1, 1992, 23:59 is 0.
The month datepart counts the number of first-of-the-months between two dates. For example, the difference between January 25 and February 2 is 1; the difference between January 1 and January 31 is 0.
When you use the date part week with datediff, you see the number of Sundays between the two dates, including the second date but not the first. For example, the number of weeks between Sunday, January 4 and Sunday, January 11 is 1.
If you use smalldatetime values, they are converted to datetime values internally for the calculation. Seconds and milliseconds in smalldatetime values are automatically set to 0 for the purpose of the difference calculation.
If the second or third argument is a date, and the datepart is hour, minute, second, or millisecond, the dates are treated as midnight.
If the second or third argument is a time, and the datepart is year, month, or day, then 0 is returned.
datediff results are truncated, not rounded, when the result is not an even multiple of the date part.
If a string is given as an argument in place of the chronological value the server interprets it as a datetime value regardless of its apparent precision. This default behavior may be changed by setting the configuration parameter builtin date strings or the set option builtin_date_strings. When these options are set, the server interprets strings given to chronological builtins as bigdatetimes. See the System Administration Guide for more information.
When a datepart of microseconds is given to this built-in, string values are always interpreted as bigdatetime.
For the smaller time units, there are overflow values, and the function returns an overflow error if you exceed these limits:
- Microseconds:approx 3 days
- Milliseconds: approx 24 days
- Seconds: approx 68 years
- Minutes: approx 4083 years
- Others: No overflow limit

See also System Administration Guide, Transact-SQL Users Guide, and select and where clause in Reference Manual: Commands.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute datediff.