MySQL Tutorial/Date Time Functions/DATE ADD

Adding one day to a date: SELECT DATE_ADD("2006-05-01",INTERVAL 1 DAY);

mysql>
mysql> SELECT DATE_ADD("2006-05-01",INTERVAL 1 DAY);
+---------------------------------------+
| DATE_ADD("2006-05-01",INTERVAL 1 DAY) |
+---------------------------------------+
| 2006-05-02                            |
+---------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>

Adding one month to a date: SELECT DATE_ADD("2006-05-01",INTERVAL 1 MONTH);

mysql>
mysql>
mysql> SELECT DATE_ADD("2006-05-01",INTERVAL 1 MONTH);
+-----------------------------------------+
| DATE_ADD("2006-05-01",INTERVAL 1 MONTH) |
+-----------------------------------------+
| 2006-06-01                              |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql>

Adding one month to start date

mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
    ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
|    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
|    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
|    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
|    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
|    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
|    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT DATE_ADD(start_date,INTERVAL 1 MONTH) from Employee;
+---------------------------------------+
| DATE_ADD(start_date,INTERVAL 1 MONTH) |
+---------------------------------------+
| 1996-08-25                            |
| 1976-04-21                            |
| 1979-01-12                            |
| 1982-11-24                            |
| 1984-02-15                            |
| 1987-08-30                            |
| 1991-01-31                            |
| 1996-10-17                            |
+---------------------------------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>

Adding one year to a date: SELECT DATE_ADD("2006-05-01",INTERVAL 1 YEAR);

mysql>
mysql> SELECT DATE_ADD("2006-05-01",INTERVAL 1 YEAR);
+----------------------------------------+
| DATE_ADD("2006-05-01",INTERVAL 1 YEAR) |
+----------------------------------------+
| 2007-05-01                             |
+----------------------------------------+
1 row in set (0.02 sec)

DATE_ADD(curdate(), INTERVAL "1 1:1:1" DAY_SECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL "1 1:1:1" DAY_SECOND);
+----------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL "1 1:1:1" DAY_SECOND) |
+----------------------------------------------------+
| 2007-07-23 01:01:01                                |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL "1 1:1" DAY_MINUTE);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL "1 1:1" DAY_MINUTE);
+--------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL "1 1:1" DAY_MINUTE) |
+--------------------------------------------------+
| 2007-07-23 01:01:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL "1:1:1" HOUR_SECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL "1:1:1" HOUR_SECOND);
+---------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL "1:1:1" HOUR_SECOND) |
+---------------------------------------------------+
| 2007-07-22 01:01:01                               |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL "1 1" DAY_HOUR);(wihtout :)

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL "1 1" DAY_HOUR);
+----------------------------------------------+
| DATE_ADD(curdate(), INTERVAL "1 1" DAY_HOUR) |
+----------------------------------------------+
| 2007-07-23 01:00:00                          |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1.1 DAY_MICROSECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 DAY_MICROSECOND);
+---------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 DAY_MICROSECOND) |
+---------------------------------------------------+
| 2007-07-22 00:00:01.000001                        |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1.1 HOUR_MICROSECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 HOUR_MICROSECOND);
+----------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 HOUR_MICROSECOND) |
+----------------------------------------------------+
| 2007-07-22 00:00:01.000001                         |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL "1:1" HOUR_MINUTE);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL "1:1" HOUR_MINUTE);
+-------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL "1:1" HOUR_MINUTE) |
+-------------------------------------------------+
| 2007-07-22 01:01:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1.1 HOUR_MINUTE); (with dot)

mysql>
mysql>
mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 HOUR_MINUTE);
+-----------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 HOUR_MINUTE) |
+-----------------------------------------------+
| 2007-07-22 01:01:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(curdate(), INTERVAL 1.1 HOUR_SECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 HOUR_SECOND);
+-----------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 HOUR_SECOND) |
+-----------------------------------------------+
| 2007-07-22 00:01:01                           |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1.1 MINUTE_MICROSECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 MINUTE_MICROSECOND);
+------------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 MINUTE_MICROSECOND) |
+------------------------------------------------------+
| 2007-07-22 00:00:01.000001                           |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1.1 MINUTE_SECOND);

mysql>
mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 MINUTE_SECOND);
+-------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 MINUTE_SECOND) |
+-------------------------------------------------+
| 2007-07-22 00:01:01                             |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1.1 SECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 SECOND);
+------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 SECOND) |
+------------------------------------------+
| 2007-07-22 00:00:01                      |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1.1 SECOND_MICROSECOND)

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);
+------------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 SECOND_MICROSECOND) |
+------------------------------------------------------+
| 2007-07-22 00:00:01.100000                           |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1.1 SECOND_MICROSECOND); (with dot)

mysql>
mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);
+------------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1.1 SECOND_MICROSECOND) |
+------------------------------------------------------+
| 2007-07-22 00:00:01.100000                           |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL "1.1" YEAR_MONTH); (with dot)

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL "1.1" YEAR_MONTH);
+------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL "1.1" YEAR_MONTH) |
+------------------------------------------------+
| 2008-08-22                                     |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL "1-1" YEAR_MONTH); (with minus sign)

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL "1-1" YEAR_MONTH);
+------------------------------------------------+
| DATE_ADD(curdate(), INTERVAL "1-1" YEAR_MONTH) |
+------------------------------------------------+
| 2008-08-22                                     |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1 DAY);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 DAY);
+-------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 DAY) |
+-------------------------------------+
| 2007-07-23                          |
+-------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(curdate(), INTERVAL 1 HOUR);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 HOUR);
+--------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 HOUR) |
+--------------------------------------+
| 2007-07-22 01:00:00                  |
+--------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1 MICROSECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 MICROSECOND);
+---------------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 MICROSECOND) |
+---------------------------------------------+
| 2007-07-22 00:00:00.000001                  |
+---------------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(curdate(), INTERVAL 1 MINUTE);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 MINUTE);
+----------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 MINUTE) |
+----------------------------------------+
| 2007-07-22 00:01:00                    |
+----------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1 MONTH);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 MONTH);
+---------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 MONTH) |
+---------------------------------------+
| 2007-08-22                            |
+---------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(curdate(), INTERVAL 1 QUARTER);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 QUARTER);
+-----------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 QUARTER) |
+-----------------------------------------+
| 2007-10-22                              |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1 SECOND);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 SECOND);
+----------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 SECOND) |
+----------------------------------------+
| 2007-07-22 00:00:01                    |
+----------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(curdate(), INTERVAL 1 WEEK);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 WEEK);
+--------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 WEEK) |
+--------------------------------------+
| 2007-07-29                           |
+--------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(curdate(), INTERVAL 1 YEAR);

mysql>
mysql> select DATE_ADD(curdate(), INTERVAL 1 YEAR);
+--------------------------------------+
| DATE_ADD(curdate(), INTERVAL 1 YEAR) |
+--------------------------------------+
| 2008-07-22                           |
+--------------------------------------+
1 row in set (0.00 sec)
mysql>

DATE_ADD(date,INTERVAL expr unit)

expr is an expression specifying the interval value.

expr is a string.

expr may start with a "-" for negative intervals.

unit is a keyword indicating the units in which the expression should be interpreted.

The following table shows the expected form of the expr argument for each unit value.

unit Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS YEAR YEARS SECOND_MICROSECOND "SECONDS.MICROSECONDS" MINUTE_MICROSECOND "MINUTES.MICROSECONDS" MINUTE_SECOND "MINUTES:SECONDS" HOUR_MICROSECOND "HOURS.MICROSECONDS" HOUR_SECOND "HOURS:MINUTES:SECONDS" HOUR_MINUTE "HOURS:MINUTES" DAY_MICROSECOND "DAYS.MICROSECONDS" DAY_SECOND "DAYS HOURS:MINUTES:SECONDS" DAY_MINUTE "DAYS HOURS:MINUTES" DAY_HOUR "DAYS HOURS" YEAR_MONTH "YEARS-MONTHS"

Date arithmetic operations require complete dates

mysql>
mysql> SELECT DATE_ADD("2006-07-00", INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD("2006-07-00", INTERVAL 1 DAY) |
+----------------------------------------+
| NULL                                   |
+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

If the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month.

mysql>
mysql> SELECT DATE_ADD("1998-01-30", INTERVAL 1 MONTH);
+------------------------------------------+
| DATE_ADD("1998-01-30", INTERVAL 1 MONTH) |
+------------------------------------------+
| 1998-02-28                               |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>

If you add to or subtract from a date value something that contains a time part, the result is automatically converted to a datetime value.

mysql>
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD("1999-01-01", INTERVAL 1 DAY) |
+----------------------------------------+
| 1999-01-02                             |
+----------------------------------------+
1 row in set (0.00 sec)
mysql>

SELECT "2005-03-32" + INTERVAL 1 MONTH;

mysql>
mysql>
mysql> SELECT "2005-03-32" + INTERVAL 1 MONTH;
+---------------------------------+
| "2005-03-32" + INTERVAL 1 MONTH |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

SELECT DATE_ADD("1992-59:59.000002", "1.999999" SECOND_MICROSECOND);

mysql>
mysql> SELECT DATE_ADD("1992-12-31 23:59:59.000002", "1.999999" SECOND_MICROSECOND);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ""1.999999" SECOND_MICROSECOND)" at line 1
mysql>

SELECT DATE_ADD("1997-59:59", "1:1" MINUTE_SECOND);

mysql>
mysql> SELECT DATE_ADD("1997-12-31 23:59:59", "1:1" MINUTE_SECOND);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ""1:1" MINUTE_SECOND)" at line 1
mysql>

SELECT DATE_ADD("1997-59:59", 1 DAY);

mysql>
mysql> SELECT DATE_ADD("1997-12-31 23:59:59", 1 DAY);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "1 DAY)" at line 1
mysql>

SELECT DATE_ADD("1997-59:59", 1 SECOND);

mysql>
mysql> SELECT DATE_ADD("1997-12-31 23:59:59", 1 SECOND);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "1 SECOND)" at line 1
mysql>

SELECT DATE_ADD("1998-00:00", "-1 10" DAY_HOUR);

mysql>
mysql> SELECT DATE_ADD("1998-01-01 00:00:00", "-1 10" DAY_HOUR);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ""-1 10" DAY_HOUR)" at line 1
mysql>

SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR);

mysql>
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR);
+-----------------------------------------+
| DATE_ADD("1999-01-01", INTERVAL 1 HOUR) |
+-----------------------------------------+
| 1999-01-01 01:00:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql>

SELECT DATE_SUB("1998-00:00", "1 1:1:1" DAY_SECOND);

mysql>
mysql> SELECT DATE_SUB("1998-01-01 00:00:00", "1 1:1:1" DAY_SECOND);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ""1 1:1:1" DAY_SECOND)" at line 1
mysql>

SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);

mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB("1998-01-02", INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02                              |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql>