How Can I Add a Year to a Date Value in a MySQL Table?

Updating Date Values in MySQL: Adding a Year

In MySQL, you can increment numerical values in a table using the operator. However, when it comes to dates, a different approach is required to add a specified period of time.

Using DATE_ADD

To add one year to a date value in a MySQL table, you can use the DATE_ADD function. Its syntax is as follows:

DATE_ADD(date, INTERVAL period)

where:

• date is the date value you want to increment

• period is the interval you want to add, specified as either:

  • A number followed by a time unit (e.g., 1 YEAR)
  • A predefined interval (e.g., YEAR(1))

In your case, to add one year to a date column, you would use the following query:

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

For example, if you have a table named events with a column called event_date, you could execute the following query to increment all event dates by one year:

UPDATE events SET event_date = DATE_ADD(event_date, INTERVAL 1 YEAR)

Alternative Method: ADDDATE

You can also use the ADDDATE function, which is an alias for DATE_ADD. The syntax for ADDDATE is slightly different:

ADDDATE(date, interval)

where:

• date is the date value you want to increment
• interval is the interval you want to add, specified as a string (e.g., '1 YEAR')

Using ADDDATE, the query to add one year to the event_date column would be:

UPDATE events SET event_date = ADDDATE(event_date, '1 YEAR')

Remember, when using either DATE_ADD or ADDDATE, ensure that the date column is of the appropriate data type, such as DATE or DATETIME.

The above is the detailed content of How Can I Add a Year to a Date Value in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!