Curriculum
Course: SQL
Login

Curriculum

SQL

SQL References

0/80

MySQL Functions

0/139

SQL Server Functions

0/84

SQL Quick Ref

0/1
Text lesson

DATEDIFF

Example

Calculate and return the difference between two date values, expressed in years.

SELECT DATEDIFF(year, ‘2017/08/25’‘2011/08/25’AS DateDiff;

Definition and Usage

The DATEDIFF() function computes and returns the difference between two dates as an integer value.

Syntax

DATEDIFF(intervaldate1date2)

Parameter Values

Parameter

Description

interval

Required: Specify the unit of time to return. Use one of the following values:

  • year, yyyy, yy: Year
  • quarter, qq, q: Quarter
  • month, mm, m: Month
  • dayofyear: Day of the 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

 

date1, date2

Required: Specify the two dates between which to calculate the difference.

Technical Details

Return type:

int

Works in:

SQL Server (beginning from 2008), Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse

More Examples

Example

Calculate and return the difference between two date values, expressed in months.

SELECT DATEDIFF(month, ‘2017/08/25’‘2011/08/25’AS DateDiff;

Example

Calculate and return the difference between two date values, expressed in hours.

SELECT DATEDIFF(hour, ‘2017/08/25 07:00’‘2017/08/25 12:45’AS DateDiff;