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


See Also:

Share on Google Plus
    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment