Syntax error How MySQL performs date arithmetic with addition and subtraction operators?

How MySQL performs date arithmetic with addition and subtraction operators?

MySQL can perform date arithmetic with addition and subtraction operators by adding together INTERVAL keyword with a unit of time, date or datetime.

Example1

Adding 2 days to a particular date.

mysql> Select '2017-05-20' + INTERVAL 2 day;
+-------------------------------+
| '2017-05-20' + INTERVAL 2 day |
+-------------------------------+
| 2017-05-22                    |
+-------------------------------+
1 row in set (0.00 sec)

Example2

Subtracting 2 days from a particular date.

mysql> Select '2017-05-20' - INTERVAL 2 day;
+-------------------------------+
| '2017-05-20' - INTERVAL 2 day |
+-------------------------------+
| 2017-05-18                    |
+-------------------------------+
1 row in set (0.00 sec)

Example 3

Adding 2 hours in time.

mysql> Select '2017-05-20 05:04:35' + INTERVAL 3 hour;
+-----------------------------------------+
| '2017-05-20 05:04:35' + INTERVAL 3 hour |
+-----------------------------------------+
| 2017-05-20 08:04:35                     |
+-----------------------------------------+
1 row in set (0.00 sec)

Example 4

Adding one month to a particular date

mysql> Select '2017-05-20 05:04:35' + INTERVAL 1 month;
+------------------------------------------+
| '2017-05-20 05:04:35' + INTERVAL 1 month |
+------------------------------------------+
| 2017-06-20 05:04:35                      |
+------------------------------------------+
1 row in set (0.00 sec)

In this way with the help of INTERVAL keyword, we can perform date arithmetic.

Updated on: 2020-06-19T13:45:14+05:30

651 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements