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.
Where startdate and enddate are valid date expressions and datepart can be one of the following:
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:
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