How to add and subtract day, month, year in date through MySql Query

MySql offer two quick function to add and subtract date through MySql query, you can directly add or subtract day, month, year on any given date using your MySql query. In below example I’ll show you how to add/subtract x number of days, month and year in date through MySql query. So that you don’t need php to for adding/subtract amount of times in dates.

MySql Add Date – DATE_ADD(date, INTERVAL value unit)

For adding days, month, year in mysql date you can use DATE_ADD in MySql.

Adding X Days

If you want to get date after X number of days then use below query for adding X days in your current date.

SELECT DATE_ADD(NOW(), INTERVAL X DAY);
# Where X will be : 1,2,3....
SELECT DATE_ADD(NOW(), INTERVAL 3 DAY);

SELECT DATE_ADD(NOW(), INTERVAL X MONTH);
# Where X will be : 1,2,3....
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);
# output will be next month date for same day.

SELECT DATE_ADD(NOW(), INTERVAL X YEAR);
# Where X will be : 1,2,3....
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
# output will be next year date for same day.

SELECT DATE_SUB(NOW(), INTERVAL X DAY);
# Where X will be : 1,2,3....
SELECT DATE_SUB(NOW(), INTERVAL 3 DAY);

SELECT DATE_SUB(NOW(), INTERVAL X MONTH);
# Where X will be : 1,2,3....
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
# output will be past month date for same day.

SELECT DATE_ADD(NOW(), INTERVAL X YEAR);
# Where X will be : 1,2,3....
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
# output will be past year date for same day.