Dec 17, 2014

DATEDIFF funtion in sql server

DATEDIFF function is return difference between two date value based on datepart specified.
Santax:
DATEDIFF (datepart , startdate , enddate)
The <Datepart> parameter specifies on which part of the date to return a new value.
Valid values are
YEAR or YY,
QUARTER or QQ or Q,
MONTH or MM or M,
DAYOFYEAR or DY or Y,
DAY or DD or D,
WEEK or WK or WW,
WEEKDAY or DW or W,
HOUR or HH,
MINUTE or MI or N,
SECOND or SS or S ,
and MILLISECOND or ms.
<Number> use to increment or decrement of <datepart> for new date,
<DATE> parameter is an expression that returns a DATETIME or SMALLDATETIME value.


EXAMPLE

SELECT DATEDIFF(YY,'12/12/2012','12/12/2014')
SELECT DATEDIFF(MM,'12/12/2012','12/12/2014')
SELECT DATEDIFF(DD,'12/12/2012','12/12/2014')
SELECT DATEDIFF(HH,'12/12/2012','12/12/2014')
SELECT DATEDIFF(SS,'12/12/2012','12/12/2014')
SELECT DATEDIFF(MS,'12/10/2014','12/12/2014')

NOTE:  For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years.