DATE_ADD( )

Using the date or datetime given, this MySQL function adds the number of intervals specified. It's fairly synonymous with the ADDDATE( ) function.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

DATE_ADD(date, INTERVAL number type)

Explanation

Using the date or datetime given, this function adds the number of intervals specified. It's fairly synonymous with the ADDDATE( ) function. If none of the parameters include datetime or time factors, the results will be returned in date format. Otherwise, the results will be in datetime format. See table below for a list of intervals permitted.

Examples

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

In this example, the appointment date is changed to its current value plus one additional day to postpone the appointment by a day. If we changed the 1 to -1, MySQL would subtract a day instead. This would make the function the equivalent of DATE_SUB( ).

If you leave out some numbers in the second argument, MySQL assumes that the leftmost interval factors are zero and are just not given. In the following example, although we're using the interval HOUR_SECOND, we're not giving the number of hours and the function still works —assuming we don't mean 5 hours, 30 minutes later. MySQL assumes here that we mean '00:05:30' and not '05:30:00'.

SELECT NOW( ) AS 'Now', 
DATE_ADD(NOW( ), INTERVAL '05:30' HOUR_SECOND) 
AS 'Later';

+---------------------+---------------------+
| Now                 | Later               |
+---------------------+---------------------+
| 2007-03-14 10:57:05 | 2007-03-14 11:02:35 | 
+---------------------+---------------------+

When adding the intervals MONTH, YEAR, or YEAR_MONTH to a date, if the given date is valid, but the results would be an invalid date because it would be beyond the end of a month, the results are adjusted to the end of the month.

SELECT DATE_ADD('2009-01-29', INTERVAL 1 MONTH)
AS 'One Month Later';

+-----------------+
| One Month Later |
+-----------------+
| 2009-02-28      | 
+-----------------+

The table below shows the intervals that may be used and how the data should be ordered. For interval values that require more than one factor, a delimiter is used and the data must be enclosed in quotes. Other delimeters may be used besides those shown in the table. For example, 'hh|mm|ss' could be used for HOUR_SECOND. In case you hadn't noticed, the names for intervals involving more than two time factors use the name of the first and last factor (e.g., DAY_MINUTE and not DAY_HOUR_MINUTE). Keep that in mind when trying to remember the correct interval.

  • DAY
  • dd

  • DAY_HOUR
  • 'dd hh'

  • DAY_MICROSECOND
  • 'dd.nn'

  • DAY_MINUTE
  • 'dd hh:mm'

  • DAY_SECOND
  • 'dd hh:mm:ss'

  • HOUR
  • hh

  • HOUR_MICROSECOND
  • 'hh.nn'

    HOUR_MINUTE

    'hh:mm'

  • HOUR_SECOND
  • 'hh:mm:ss'

  • MICROSECOND
  • nn

  • MINUTE
  • mm

  • MINUTE_MICROSECOND
  • 'mm.nn'

  • MINUTE_SECOND
  • 'mm:ss'

  • MONTH
  • mm

  • QUARTER
  • qq

  • SECOND
  • ss

  • SECOND_MICROSECOND
  • 'ss.nn'

  • WEEK
  • ww

  • YEAR
  • yy

  • YEAR_MONTH
  • 'yy-mm'