Update date + one year in mysql

When I want setting numerical value +1 in mysql table, I use e.g.:

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

How c开发者_如何学编程an I set date + one year?

Thanks

You could use DATE_ADD : (or ADDDATE with INTERVAL)

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

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

Should you want to add more complex time periods, for example 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 doesn't allow for adding more than one interval. And there is no YEAR_DAYS interval keyword, though there are others that combine time periods. If you are adding times, use now() rather than curdate().

For multiple interval types use a nested construction as in:

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

For updating a given date in the column date to 1 year + 1 day