The SQL DATEDIFF() function is used to get the date/time difference of 2 date expressions. It is supported by both SQL Server and MySQL, but the syntax is different.
SQL CASE Syntax
MySQL:
DATEDIFF(Date1, Date2)
Date1 and Date2 can be any valid date/time expression, but only the date part will be calculated, it will return the difference in days.
SQL Server:
DATEDIFF(datepart, Date1, Date2)
Date1 and Date2 can be any valid date/time expression, and datepart can be anyone of the following:
| datepart | abbreviation |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
| TZoffset | tz |
| ISO_WEEK | sowk, isoww |
Note: in MySQL the result = Date1 – Date2, but in SQL Server the result = Date2 – Date1.
SQL DATEDIFF Examples
MySQL Examples:
SELECT DATEDIFF('2011-09-05 13:00:00','2011-09-08')
The result will be: -3
SELECT DATEDIFF('2011-10-02','2011-09-08')
The result will be: 24
SELECT DATEDIFF('2011-09-20 8:12:00','2011-09-20 10:23:00')
The result will be: 0
SQL Server Examples:
SELECT DATEDIFF(yyyy, '2010-09-01', '2011-07-01')
The result will be: 1
SELECT DATEDIFF(hh, '2011-09-01', '2011-07-01')
The result will be: -1488
SELECT DATEDIFF(hh, '2011-09-20 8:12:00', '2011-09-20 10:23:00')
The result will be: 2