SQL Server time difference datediff Function

Address: http://sunboyyyl.blog.163.com/blog/static/224738120112874910613/

Datediff function [Date and Time]

Function: returns the interval between two dates.

Syntax
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 part of the interval to be measured.

For more information about date, see date.

Date-expression-1 The start date of an interval. Subtract this value from date-expression-2 to return the number of days of date-parts between two parameters.

Date-expression-2 end date of an interval. Subtract date-expression-1 from the value and return the number of days between the two parameters.

Usage
This function calculates the number of date parts between two specified dates. The result is a signed integer equal to (date2-date1) in the date section.

When the result is not an even multiple of the date part, 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 not the first date.

When month is used as the date part, datediff returns the number of the first day of the month between two dates (including the second date but not the first date.

When week is used as the date part, datediff returns the number of Sundays between two dates (including the second but not the first.

Overflow value for a smaller unit of time:

Milliseconds 24 days

Seconds 68 years

Minutes 4083

Others has no overflow restrictions

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 following example returns 1:

The statement under select datediff (hour, '4: 00am', '5: 50am') returns 102:

Select datediff (month, '2014/1/02 ', '2014/3/15') the following statement returns 0:

Select datediff (day, '00: 00', '23: 59') the following statement returns 4:

Select datediff (day, '2014/1/19 ', '2014/3/23') the following statement returns 0:

Select datediff (month, '2014/1/19', '2014/3/23') the following statement returns 1:

Select datediff (month, '2014/1/19', '2014/3/23 ')