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.

You can also search these topics using sql datediff function, sql date difference, sql server date add, sql server datediff days, sql datediff example, date difference sql, sql difference between dates, sql subtract dates, between dates sql, datediff sql server example, sql date between two dates, sql server dateadd example, sql compare two dates, sql dateadd example, sql server subtract dates.

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

You can also search these topics using sql days between dates, sql datediff month, sql datediff seconds, sql server datediff month, dateadd sql server example, sql server date subtract, sql server dateadd month, subtract dates sql, sql days between two dates, sql datediff format, sql time difference, sql difference between two timestamps, sql day difference, sql month function.