SQL DATEDIFF (Time difference)

DATEDIFF function [date and time]

The function returns the interval between two dates.

Grammar DATEDIFF (Date-part, date-expression-1, date-expression-2)

Date-part:year | Quarter | Month | Week | Day | Hour | Minute | Second | Millisecond

The parameter date-part specifies the date portion of the interval to be measured.

For more information about the date section, see the Date section.

Date-expression-1 the start date of a certain interval. Subtracts the value from Date-expression-2 and returns the number of days date-parts between two parameters.

Date-expression-2 the end date of a certain interval. Subtract date-expression-1 from this value to return the number of days between the two parameters date-parts.

Use this function to calculate the number of date parts between two specified dates. The result is a signed integer value equal to (date2-date1) in the date part.

When the result is not an even multiple of the date part, the DATEDIFF is truncated instead of rounded.

When day is used as the date part, DATEDIFF returns the number of midnight between two specified times, including the second date but excluding the first date.

When you use month as the date part, DATEDIFF returns the number of the first day of the month that occurs between two dates, including the second date but excluding the first date.

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

For a smaller time unit there is an overflow value:

Milliseconds 24 days

Seconds 68

Minutes 4083

Others no overflow limit

If these limits are exceeded, this function returns an overflow error.

Standard and compatibility sql/92 Transact-SQL extensions.

SQL/99 Transact-SQL extensions.

Sybase is compatible with Adaptive Server Enterprise.

The following example 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 ')

SQL DATEDIFF (Time difference)