Name

DATE_ADD( )

Synopsis

DATE_ADD(date, INTERVAL value type)

This function extracts time or date information and, thereby, adds time to the value extracted. It’s synonymous with the ADDDATE( ) function.

UPDATE appointments
   SET appt_date = DATE_ADD(appt_date, INTERVAL 1 DAY)
   WHERE rec_id='1202';

In this example, the appointment date is changed to its current value plus, one additional day (i.e., we’re postponing the appointment by one day). If we changed the 1 to a -1, MySQL would subtract a day instead. The format of value depends on the type and is shown in Table 6-1.

Table 6-1. DATE_ADD intervals and formats of values

Type of increment

Description

DAY

dd

DAY_HOUR

dd hh

DAY_MINUTE

dd hh:mm

DAY_SECOND

dd hh:mm:ss

HOUR

hh

HOUR_MINUTE

hh:mm

HOUR_SECOND

hh:mm:ss

MINUTE

mm

MINUTE_SECOND

mm:ss

MONTH

mm

SECOND

ss

YEAR

yyyy

YEAR_MONTH

yy-mm

Get MySQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.