SQL Server DATEDIFF() Function

The DATEDIFF() function in SQL Server calculates the difference between two dates. This is a very useful function for working with dates and performing time-based analysis or reporting.

DATEDIFF(): Definition and Usage

DATEDIFF() lets you determine the difference between two dates in various units (years, months, days, hours, minutes, seconds, milliseconds). It returns the difference as an integer. The order of the dates matters (date2 - date1).

Syntax

Syntax

DATEDIFF(interval, date1, date2)

Parameter Values

Parameter Description
interval The unit of time for the difference. Options:
  • year, yyyy, yy: Year
  • quarter, qq, q: Quarter
  • month, mm, m: Month
  • dayofyear: Day of year
  • day, dy, y: Day
  • week, ww, wk: Week
  • weekday, dw, w: Weekday
  • hour, hh: Hour
  • minute, mi, n: Minute
  • second, ss, s: Second
  • millisecond, ms: Millisecond
This is required.
date1, date2 The two dates to compare. The order matters (date2 - date1). Both are required.

Examples

Calculating the Difference in Years

This example calculates the difference in years between August 25th, 2011 and August 25th, 2017.

Syntax

SELECT DATEDIFF(year, '2011/08/25', '2017/08/25') AS DateDiff;
Output

6

Calculating the Difference in Months

Calculating the difference in months between the same two dates.

Syntax

SELECT DATEDIFF(month, '2011/08/25', '2017/08/25') AS DateDiff;
Output

72

Calculating the Difference in Hours

This calculates the difference in hours between two datetime values.

Syntax

SELECT DATEDIFF(hour, '2017/08/25 07:00', '2017/08/25 12:45') AS DateDiff;
Output

5