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);

Adding X Months

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

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.

Adding X Years

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

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.



MySql Subtract Date – DATE_SUB(date, INTERVAL value unit)

For Subtracting days, month, year in mysql date you can use DATE_SUB in MySql.

Subtracting X Days

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

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

Subtracting X Months

If you want to get date before X number of months then use below query for subtracting X months in your current date.

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.

Subtracting X Years

If you want to get date before X number of years then use below query for subtracting X years in your current date.

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.




You can pass below units for adding or subtracting amount for time in date through MySql query

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH