DATEDIFF() function is very useful when we are working with date operations. DATEDIFF function return the difference between date items in days, weeks, minutes, years and hours. It can also used with WHERE clause.
Summery
DATEDIFF(datepart,startdate,enddate)
Where startdate and enddate are valid date expressions and datepart can be one of the following:
datepart | Abbreviation |
---|---|
year | yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy, y |
day | dd, d |
week | wk, ww |
weekday | dw, w |
hour | hh |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
Example
The following statements have the same startdate and the same enddate and returns the date difference in year, month, day and hour respectively.
SELECT DATEDIFF(YEAR, '2011-2-1 23:59:59', '2012-01-01 00:00:00') AS years SELECT DATEDIFF(MONTH, '2011-2-1 23:59:59', '2012-01-01 00:00:00') AS months SELECT DATEDIFF(DAY,'2011-2-1 23:59:59', '2012-01-01 00:00:00') AS days SELECT DATEDIFF(HOUR, '2011-2-1 23:59:59', '2012-01-01 00:00:00') AS hours
Output will be:
years
———
1
months
———
11
days
———
334
hours
———
7993
DATEDIFF() function can be use with select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.
for example see the following query:
SELECT * FROM tbl_Employee WHERE DATEDIFF(YEAR, DateOfBirth, GetDate())>50
the above query will return all the employee data which has age greater then 50.
GETDATE function returns the current date time of your system