Datediff function [Date and Time]
Function
Returns the interval between two dates.
Syntax
Datediff (date-part, date-expression1, date-expression2)
Date-part:
Year | quarter | month | week | Day | hour | minute | second | millisecond
(Year, quarter, month, week, day, hour, minute, second, millisecond)
Parameters
Date-part specifies the date part of the interval to be calculated.
The start date of an interval in the date-expression1. Subtract this value from the date-expression2 and return the value of date-parts between two parameters.
The end date of an interval in the date-expression2. Subtract the Date-expression1 from this value and return the value of date-parts between two parameters.
Example:
The following statement returns 1:
Select datediff (hour, '4: 00am', '5: 50am ')
The following statement 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 ')
Usage
This function calculates the value of the date part 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.
Trademanager navigation