DateDiff is commonly used to calculate date difference functions, using the following syntax:
DATEDIFF (DatePart, StartDate, EndDate)
Where parameter datepart has the following options (only common parameters are listed):
DatePart |
Abbreviation |
Explain |
Year |
Yy,yyyy |
Years |
Month |
Mm,m |
Month |
Day |
Dd,d |
Days |
Week |
Wk,w |
Week |
Hour |
hh |
Hours |
Minute |
Mi,n |
Minutes |
Second |
Ss,s |
Seconds |
Millisecond |
Ms |
Milliseconds |
The following examples illustrate:
- Calculate the number of years between two dates
SELECT DATEDIFF (year,'2014-01-01', GETDATE ()) as years
You can also use an abbreviated form
SELECT DATEDIFF (YY,'2014-01-01', GETDATE ()) as years
Or
SELECT DATEDIFF (YYYY,'2014-01-01', GETDATE ()) as years
return result 3
- Calculate the number of months between two dates
SELECT DATEDIFF (MONTH,'2017-01-01', GETDATE ()) as MONTHS
Return result 5
Other types of usage are about the same, not much more.
DateDiff of MS SQL system functions