Date Function : DATEDIFF()

This is used to find the difference between two dates based on DatePart type

The DATEDIFF() function returns the time between two dates.

Syntax:

DATEDIFF(DatePart,StartDate,EndDate)

DatePart is Abbreviation used same as in DatePart() function.

StartDate and EndDate are valid date time.

Example:

SELECT DATEDIFF(day,'2015-01-23 10:00:44.470','2015-10-23 10:00:44.470') AS DiffDate

--The Difference between millisecond, nanosecond and microsecond should be less otherwise it will throw below error.

Msg 535, Level 16, State 0, Line 1

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

SELECT DATEDIFF(millisecond,'2015-10-23 10:00:43.470','2015-10-23 10:00:44.470') AS DiffDate

OutPut:

DatePart	Type	Return Value
year	yy, yyyy	1
quarter	qq, q	7
month	mm, m	21
dayofyear	dy, y	638
day	dd, d	638
week	wk, ww	91
weekday	dw, w	638
hour	hh	15312
minute	mi, n	918720
second	ss, s	55123200
millisecond	ms	100
microsecond	mcs	1000000
nanosecond	ns	1000000000