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.

You can also search these topics using sql date_add function, sql server dateadd, sql server date add, sql dateadd function, sql server dateadd example, sql dateadd example, add days to date sql, date query in sql, sql date arithmetic, sql add months to date.

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

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
You can also search these topics using sql server subtract dates, sql server date_sub, sql server date add days, sql getdate 1 day, sql subtract dates, sql date subtract, sql date minus 1 day, subtract dates sql, sql date add 1 year, sql server add months.