SQL DATEDIFF Function

Monday, December 19, 2011

tags SQL

The DATEDIFF function is used in Microsoft SQL Server to return the time between two dates/date columns.

Employees Table

employeeIDemployeeNamehireDate
1000John Smith1995-12-03
1001Fred White2001-10-12
1002Jane Scott1998-05-01
1003Samuel Williams1991-01-03


In this example, we want to find out how many days an employee has been working for the organizaiton since their hire date.


Syntax

DATEDIFF(datepart, startDate, endDate)


DATEPART Reference Table

datepartabbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
dayd
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
TZoffsettz
ISO_WEEKisowk, isoww


Example
SELECT employeeName as [Employee Name], DATEDIFF(day,hiredate,GETDATE()) as [Total Days Since Hire]
FROM employees


Results (Query ran on 12/13/2011)

Employee NameTotal Days Since Hire
John Smith5854
Fred White3714
Jane Scott4974
Samuel Williams7649

The SQL DATEDIFF function can be very useful when you are interested in finding out how much time there is between two dates, a date and a date field, or two date fields.

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

SQL Cookbook Head First SQL: Your Brain on SQL A Learners Guide