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.


Search Keys

  • sql datediff function
  • sql datediff
  • sql date difference
  • sql server date add
  • sql server datediff days
  • sql datediff example
  • datediff function
  • date difference sql
  • sql diff date
  • sql difference between dates
  • sql subtract dates
  • between dates sql
  • datediff example
  • 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 DeliveryDate
1 MySQL: The Complete Reference 23-03-2006 17:33:45
2 Teach Yourself SQL in 10 Minutes 16-02-1997 20:31:28
3 Transact SQL Cookbook 28-03-2007 18:16:50
4 Advanced SQL Programming 19-10-1996 18:15:29
5 Oracle Fundamentals 28-06-1996 20:28:58
6 Teach Yourself SQL 17-07-2011 14:05:06
7 Foundations Of Sql Server 2008 19-02-1996 03:05:28
8 Microsoft SQL Server 2012 Bible 25-04-2007 07:55:09
9 Understanding the New SQL 23-10-2000 15:04:57
10 SQL For Microsoft Access 26-02-2000 15:48:21
11 MySql Interview Questions 26-09-1997 10:25:18
12 SQL All-in-One For Dummies 24-04-2008 20:48:01

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
26-06-1996 21:10:46 28-06-1996 20:28:58 2
12-07-2011 10:58:33 17-07-2011 14:05:06 5
11-02-1996 10:48:55 19-02-1996 03:05:28 8
19-03-2007 11:26:18 25-04-2007 07:55:09 37
19-09-2000 02:40:04 23-10-2000 15:04:57 34
13-02-2000 21:06:20 26-02-2000 15:48:21 13
19-08-1997 21:47:52 26-09-1997 10:25:18 38
18-04-2008 09:21:30 24-04-2008 20:48:01 6

Search Keys

  • datediff year
  • sql days between dates
  • sql datediff month
  • sql datediff seconds
  • sql server datediff month
  • dateadd sql server example
  • sql server date subtract
  • sql date query
  • 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 count days
  • sql day number
  • days between sql
  • sql month function