DATEDIFF() function in SQL Server

 
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:

datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns

 

Example

The following statements have the same startdate and the same enddate and returns the date difference in year, month, day and hour respectively.

  1. SELECT DATEDIFF(year, '2011-2-1 23:59:59', '2012-01-01 00:00:00') AS years
  2. SELECT DATEDIFF(month, '2011-2-1 23:59:59', '2012-01-01 00:00:00') AS months
  3. SELECT DATEDIFF(day,'2011-2-1 23:59:59', '2012-01-01 00:00:00') AS days
  4. 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:

  1. 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