SQL DATEDIFF() Function
The SQL DATEDIFF() is a function, and use to find the difference between two dates.
The SQL DATEDIFF() function returns a long value specifying the number of time intervals between two specified dates. We can get the difference in any type of datepart format like difference in days, month, year and etc.
It can be used in SELECT statement as well in where clause.
Related Links
SQL DATEDIFF() Syntax
The below syntax is used to get differences between two dates or datetime values.
For SQL Server
SELECT DATEDIFF(interval_datepart_value, 'early_valid_datetime_value', 'later_valid_datetime_value');
For MS Access
SELECT DATEDIFF('interval_datepart_value', 'early_valid_datetime_value', 'later_valid_datetime_value');
For MySql
SELECT DATEDIFF('later_valid_datetime_value', 'early_valid_datetime_value');
Note:- The MySql DATEDIFF() function will return the differences only in days from one date to the other.
SQL DATEDIFF() Example - Using Expression Or Formula
The following SQL SELECT statement will shows the differences in hours and days between two date or datetime values.
For SQL Server
SELECT
DATEDIFF(s, '2012-04-24 18:23:44', '2012-04-24 19:38:30') AS 'Diff In Seconds',
DATEDIFF(n, '2012-04-24 18:23:44', '2012-04-24 19:38:30') AS 'Diff In Minutes',
DATEDIFF(Hh, '2012-04-24 18:23:44', '2012-04-24 19:38:30') AS 'Diff In Hours';
For MS Access
SELECT
DATEDIFF('s', '2012-04-24 18:23:44', '2012-04-24 19:38:30') AS 'Diff In Seconds',
DATEDIFF('n', '2012-04-24 18:23:44', '2012-04-24 19:38:30') AS 'Diff In Minutes',
DATEDIFF('h', '2012-04-24 18:23:44', '2012-04-24 19:38:30') AS 'Diff In Hours';
The result of above query is:
Diff In Seconds | Diff In Minutes | Diff In Hours |
---|---|---|
4486 | 75 | 1 |
Sample Database Table - BookOrder
BookID | BookName | OrderDate | DeliveryDate |
---|---|---|---|
1 | MySQL: The Complete Reference | 16-02-2006 22:23:38 | 23-03-2006 17:33:45 |
2 | Teach Yourself SQL in 10 Minutes | 12-02-1997 18:40:49 | 16-02-1997 20:31:28 |
3 | Transact SQL Cookbook | 25-02-2007 20:41:18 | 28-03-2007 18:16:50 |
4 | Advanced SQL Programming | 13-10-1996 14:55:24 | 19-10-1996 18:15:29 |
SQL DATEDIFF() Example - With Table Column
The following SQL SELECT statement display the column "OrderDate" and "DeliveryDate", from the "BookOrder" table. The DATEDIFF() function will get the differences between DeliveryDate and OrderDate in days and we will stored that value to a new column called "Diff In Days".
For SQL Server
SELECT
OrderDate, DeliveryDate,
DATEDIFF(d, OrderDate, DeliveryDate) AS 'Diff In Days'
FROM BookOrder;
For MS Access
SELECT
OrderDate, DeliveryDate,
DATEDIFF('d', OrderDate, DeliveryDate) AS 'Diff In Days'
FROM BookOrder;
For MySql
SELECT
OrderDate, DeliveryDate,
DATEDIFF(DeliveryDate, OrderDate) AS 'Diff In Days'
FROM BookOrder;
The result of above query is:
OrderDate | DeliveryDate | Diff In Days |
---|---|---|
16-02-2006 22:23:38 | 23-03-2006 17:33:45 | 35 |
12-02-1997 18:40:49 | 16-02-1997 20:31:28 | 4 |
25-02-2007 20:41:18 | 28-03-2007 18:16:50 | 31 |
13-10-1996 14:55:24 | 19-10-1996 18:15:29 | 6 |
Related Links