SQL DATEADD() | DATE_ADD() Function


The SQL DATEADD() | DATE_ADD() is a function, and use to add or subtract a number of time units to a date.

The SQL DATEADD() | DATE_ADD() function is use to perform an arithmatic operations on a date time value. We can specify the interval value to be added or subtracted from the given input date time value.

It can be used in SELECT statement as well in where clause.


Search Keys

  • sql date_add function
  • sql date
  • sql dateadd
  • sql server dateadd
  • sql datediff
  • sql server date add
  • sql add date
  • sql dateadd function
  • sql server dateadd example
  • sql dateadd example
  • sql date year
  • add days to date sql
  • sql date 1 day
  • date query in sql
  • sql date arithmetic
  • sql add months to date
  • dateadd hour
  • dateadd example

SQL DATEADD() | DATE_ADD() Syntax

The below syntax is used to perform an arithmatic operations on given a date time value.

For SQL Server


SELECT DATEADD(interval_datepart_value, input_number, 'valid_datetime_value');

For MS Access


SELECT DATEADD('interval_datepart_value', input_number, 'valid_datetime_value');

For MySql


SELECT DATE_ADD('valid_datetime_value', INTERVAL input_number interval_datepart_value);


SQL DATEADD() | DATE_ADD() Example - Using Expression Or Formula

The following SQL SELECT statement will add some interval time an existing date time value from a given input date value. We use now() function to get the input date value.

For SQL Server


SELECT 
NOW() AS 'Current Date and Time', 
DATEADD(s, 10, NOW()) AS '10 Seconds Added',
DATEADD(n, -5, NOW()) AS '-5 Minutes Added',
DATEADD(d, -2, NOW()) AS '-2 Days Added',
DATEADD(m, 3, NOW()) AS '3 Months Added';

For MS Access


SELECT 
NOW() AS 'Current Date and Time', 
DATEADD('s', 10, NOW()) AS '10 Seconds Added',
DATEADD('n', -5, NOW()) AS '-5 Minutes Added',
DATEADD('d', -2, NOW()) AS '-2 Days Added',
DATEADD('m', 3, NOW()) AS '3 Months Added';

For MySql


SELECT 
NOW() AS 'Current Date and Time', 
DATE_ADD(NOW(), INTERVAL 10 SECOND) AS '10 Seconds Added',
DATE_ADD(NOW(), INTERVAL -5 MINUTE) AS '-5 Minutes Added',
DATE_ADD(NOW(), INTERVAL -2 DAY) AS '-2 Days Added',
DATE_ADD(NOW(), INTERVAL 3 MONTH) AS '3 Months Added';

The result of above query is:

Current Date and Time 10 Seconds Added -5 Minutes Added -2 Days Added 3 Month Added
15-04-2015 14:47:33 15-04-2015 14:47:43 15-04-2015 14:42:33 13-04-2015 14:47:33 15-07-2015 14:47:33

Sample Database Table - BookOrder

BookID BookName DeliveryDate
1 MySql Concurrency 26-06-2004 01:01:08
2 Oracle PL/sql By Example 22-09-2001 23:58:06
3 Oracle 11g PL/SQL Programming 20-05-2006 19:28:34
4 SQL Programming & Database Design 24-04-2003 15:41:06
5 Pro Oracle Administration 26-04-2007 18:30:30
6 The Database Language SQL 27-04-1997 09:11:19
7 Transact SQL Cookbook 23-07-2000 16:36:27
8 Oracle Interview Questions 28-05-2009 15:35:01
9 Oracle Concurrency 28-05-2002 23:31:58
10 The SQL Programming Language 27-03-2006 18:22:24
11 How to Write Accurate SQL Code 23-11-2010 22:55:31
12 Securing SQL Server 27-08-1997 13:37:57

SQL DATEADD() | DATE_ADD() Example - With Table Column

The following SQL SELECT statement display the column "DeliveryDate", from the "BookOrder" table. The DATEADD() function will add 3 days to "DeliveryDate" and we will stored that value to a new column called "3 Days Added".

For SQL Server


SELECT DeliveryDate, 
DATEADD(d, 3, DeliveryDate) AS '3 Days Added'
FROM BookOrder;

For MS Access


SELECT DeliveryDate, 
DATEADD('d', 3, DeliveryDate) AS '3 Days Added'
FROM BookOrder;

For MySql


SELECT DeliveryDate, 
DATE_ADD(DeliveryDate, INTERVAL 3 DAY) AS '3 Days Added'
FROM BookOrder;

The result of above query is:

DeliveryDate 3 Days Added
26-06-2004 01:01:08 29-06-2004 01:01:08
22-09-2001 23:58:06 25-09-2001 23:58:06
20-05-2006 19:28:34 23-05-2006 19:28:34
24-04-2003 15:41:06 27-04-2003 15:41:06
26-04-2007 18:30:30 29-04-2007 18:30:30
27-04-1997 09:11:19 30-04-1997 09:11:19
23-07-2000 16:36:27 26-07-2000 16:36:27
28-05-2009 15:35:01 31-05-2009 15:35:01
28-05-2002 23:31:58 31-05-2002 23:31:58
27-03-2006 18:22:24 30-03-2006 18:22:24
23-11-2010 22:55:31 26-11-2010 22:55:31
27-08-1997 13:37:57 30-08-1997 13:37:57

Search Keys

  • date_add oracle
  • sql server subtract dates
  • sql server date_sub
  • sql server date add days
  • sql getdate 1 day
  • sql subtract dates
  • sql date subtract
  • datediff example
  • sql date minus 1 day
  • subtract dates sql
  • sql date add 1 year
  • sql add time
  • sql add month
  • sql add year
  • sql server add months
  • sql adddays
  • sql today
  • addmonth sql