Update date + one year in mysql

Question

When I want to set the numeric value to +1 in the mysql table, I use for example:

UPDATE table SET number=number+1 WHEN ... 

How to set date + year?

thank

Answer 1

You can use DATE_ADD : (or ADDDATE with INTERVAL )

 UPDATE table SET date = DATE_ADD(date, INTERVAL 1 YEAR) 

Answer 2

This post helped me today, but I had to experiment to do what I needed. Here is what I found.

If you want to add more complex time periods, such as 1 year and 15 days, you can use

 UPDATE tablename SET datefieldname = curdate() + INTERVAL 15 DAY + INTERVAL 1 YEAR; 

I found that using DATE_ADD does not allow adding more than one interval. And there is no YEAR_DAYS interval keyword, although there are others that combine time periods. If you add time, use now() , not curdate() .

Answer 3

For several types of intervals, use a nested construct, as in:

  UPDATE table SET date = DATE_ADD(DATE_ADD(date, INTERVAL 1 YEAR), INTERVAL 1 DAY) 

To update a given date in the date column to 1 year + 1 day