dateadd  datename

Chapter 2: Transact-SQL Functions

datediff

Description

Returns the difference between two dates.

Syntax

datediff(datepart, date expression1, date expression2)

Parameters

datepart

is a date part or abbreviation. For a list of the date parts and abbreviations recognized by Adaptive Server, see “Date parts”.

date expression1

is an expression of type datetime, smalldatetime, date, time, or a character string in a datetime format.

date expression2

is an expression of type datetime, smalldatetime, date, time, or a character string in a datetime format.

Examples

Example 1

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 2

Find 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 3

Find 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 4

Find 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 5

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





Copyright © 2005. Sybase Inc. All rights reserved. datename

View this book as PDF