datediff

Description

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 Adaptive 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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute datediff.

See also

Datatypes Date and time datatypes

Commands select, where clause

Functions dateadd, datename, datepart, getdate