MySQL DATE_ADD() Function with Examples


How to Use the MySQL DATE_ADD() Function

In this tutorial, you’ll learn how to use the MySQL DATE_ADD() function. The DATE_ADD() function adds a time and date interval to a given date and then returns a new date.

MySQL DATE_ADD() Syntax

The syntax of the DATE_ADD() function is as follows:

DATE_ADD(date, INTERVAL expression unit)

Arguments

  • The date, expression, and unit are required.
  • The  unit argument is a type of interval to add. It can be one of the followings:
    • MINUTE
    • HOUR
    • SECOND
    • DAY
    • WEEK
    • QUARTER
    • HOUR_MINUTE
    • HOUR_SECOND
    • MICROSECOND
    • SECOND_MICROSECOND
    • MINUTE_MICROSECOND
    • MINUTE_SECOND
    • HOUR_MICROSECOND
    • DAY_MICROSECOND
    • DAY_SECOND
    • DAY_MINUTE
    • DAY_HOUR
    • YEAR_MONTH

Examples

Adding hours

The following statement adds 2 hours to 2019-01-10 and returns 2019-01-10 02:00:00

SELECT DATE_ADD("2019-01-01",INTERVAL 2 HOUR);

Adding seconds

The following statement adds 3 seconds to 2019-01-01 and returns 2019-01-01 00:00:03:

SELECT DATE_ADD("2019-01-01", INTERVAL 3 SECOND);

Adding months

The following statement adds three months to 2019-01-01 and returns 2019-04-01:

SELECT DATE_ADD("2019-01-01", INTERVAL 3 MONTH);

Adding years

The following statement adds one year to 2019-01-01 and returns 2020-01-01:

SELECT DATE_ADD("2019-01-01", INTERVAL 1 YEAR);

Adding weeks

The following statement adds four weeks to 2019-01-01 and returns 2019-01-29:

SELECT DATE_ADD("2019-01-01", INTERVAL 4 WEEK);

Adding hours and minutes

The following statement adds 2 hours and 20 minutes to 2019-01-01 and returns 2019-01-01 02:20:00:

SELECT DATE_ADD("2019-01-01", INTERVAL "2:20" HOUR_MINUTE);

Adding years and months

The following statement adds one year and five months to 2019-01-01 and returns 2020-06-01:

SELECT DATE_ADD("2019-01-01", INTERVAL "1:5" YEAR_MONTH);

In this tutorial, you’ve learned how to use the MySQL DATE_ADD() function. DATE_ADD() is a function to add a date and time interval to a given date and then return a new date. If you need to subtract a time and date interval from a date, use the DATE_SUB() function.

Leave a Reply

Your email address will not be published. Required fields are marked *