Verbose SQL in DateDiff usage

DATEDIFF function [date and time]

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

Parameters
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.

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

Standards and compatibility
sql/92 transact-SQL extension.

sql/99 transact-SQL extension.

Sybase is compatible with Adaptive Server Enterprise.

The statement in the following example returns 1:

The statement under SELECT DateDiff (hour, ' 4:00AM ', ' 5:50am ') 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 ')