;

How to Add and Subtract Dates from DateTime in MySQL

Tutorialsrack 24/05/2020 SQL MySQL

In this article, you will learn how to add or subtract years, months, weeks, days, hours, minutes, seconds, microseconds to a DateTime in MySQL. DateTime manipulation is the most common scenario is when we fetching data from the database or storing data in a database. Here, we used the MySQL built-in DateTime functions: ADDDATE(), DATE_ADD(), DATE_SUB(), and add and subtract dates without using any built-in function. 

This article answers your all questions as given below:

How to add or subtract 30 days or specified days to a Date in MySQL server?

How to add or subtract quarter to a Date in MySQL server?

How to add or subtract 6 Months or specified months to a specified date in MySQL Server?

How to add or subtract 5 years or specified years to a specified Date in MySQL Server?

How to add or subtract 1 week or specified weeks to a specified date in MySQL Server?

how to add or subtract hours, minutes or seconds, microseconds to a specified Date in MySQL Server?

ADDDATE() Function

This function adds a time/date interval to date and then returns the date.

Syntax
ADDDATE(date, INTERVAL value unit)

OR

Syntax
ADDDATE(date, days)

DATE_ADD() Function

This function adds a time/date interval to date and then returns the date.

Syntax
DATE_ADD(date, INTERVAL value unit)

DATE_SUB() Function

This function subtracts a time/date interval from a date and then returns the date.

Synatx
DATE_SUB(date, INTERVAL value unit)

Where,
          date = Date Value
          days = in ADDDATE() function, days equal to the number of days added to the date
          value = Integer Number
          unit = Unit of time such as DAY, WEEK, MONTH, YEAR, etc.

The type of interval to Add OR Subtract and can be one of the following values:

UNIT EXPECTED EXPR FORMAT
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND ‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND ‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND ‘MINUTES:SECONDS’
HOUR_MICROSECOND ‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND ‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE ‘HOURS:MINUTES’
DAY_MICROSECOND ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND ‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE ‘DAYS HOURS:MINUTES’
DAY_HOUR ‘DAYS HOURS’
YEAR_MONTH ‘YEARS-MONTHS’

 

Example of ADDDATE() Function

Add Unit of Time to a Date Using ADDDATE() Function

In this example, you will learn how to add years, months, weeks, days, hours, minutes, seconds, microseconds, etc. to a DateTime using ADDDATE() in MySQL.

Add Unit of Time to a Date Using ADDDATE() Function
-- --------- ADD Unit of Time To a Specific Date ----------

-- DECLARE a DATETIME Variable
SET @DateVAl = CAST('2020-04-01 10:37:57.100000' AS DATETIME);

-- ADD Days to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 30 DAY) AS 'ADD Days To a DATE';
-- Output ==> 2020-05-01 10:37:57

-- ADD Weeks to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 2 WEEK) AS 'ADD Weeks To a DATE';
-- Output ==> 2020-04-15 10:37:57

-- ADD Months to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 2 MONTH) AS 'ADD Months To a DATE';
-- Output ==> 2020-06-01 10:37:57

-- ADD Quarters to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 2 QUARTER) AS 'ADD Quarters To a DATE';
-- Output ==> 2020-10-01 10:37:57

-- ADD Years to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 2 YEAR) AS 'ADD Years To a DATE';
-- Output ==> 2022-04-01 10:37:57

-- ADD Hours to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 2 HOUR) AS 'ADD Hours To a DATE';
-- Output ==> 2020-04-01 12:37:57

-- ADD Minutes to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 15 MINUTE) AS 'ADD Minutes To a DATE';
-- Output ==> 2020-04-01 10:52:57

-- ADD Seconds to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 1500 SECOND) AS 'ADD Seconds To a DATE';
-- Output ==> 2020-04-01 11:02:57

-- ADD MicroSeconds to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL 150000 MICROSECOND) AS 'ADD MicroSeconds To a DATE';
-- Output ==> 2020-04-01 10:37:57.150000

Subtract Unit of Time to a Date Using ADDDATE() Function

In this example, you will learn how to subtract years, months, weeks, days, hours, minutes, seconds, microseconds, etc. from a DateTime using ADDDATE() in MySQL.

Subtract Unit of Time to a Date Using ADDDATE() Function
-- --------- Subtract Unit of Time To a Specific Date ----------

-- DECLARE a DATETIME Variable
SET @DateVAl = CAST('2020-04-01 10:37:57.100000' AS DATETIME);

-- Subtract Days to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -30 DAY) AS 'Subtract Days To a DATE';
-- Output ==> 2020-03-02 10:37:57

-- Subtract Weeks to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -2 WEEK) AS 'Subtract Weeks To a DATE';
-- Output ==> 2020-03-18 10:37:57

-- Subtract Months to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -2 MONTH) AS 'Subtract Months To a DATE';
-- Output ==> 2020-02-01 10:37:57

-- Subtract Quarters to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -2 QUARTER) AS 'Subtract Quarters To a DATE';
-- Output ==> 2019-10-01 10:37:57

-- Subtract Years to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -2 YEAR) AS 'Subtract Years To a DATE';
-- Output ==> 2018-04-01 10:37:57

-- Subtract Hours to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -2 HOUR) AS 'Subtract Hours To a DATE';
-- Output ==> 2020-04-01 08:37:57

-- Subtract Minutes to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -15 MINUTE) AS 'Subtract Minutes To a DATE';
-- Output ==> 2020-04-01 10:22:57

-- Subtract Seconds to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -1500 SECOND) AS 'Subtract Seconds To a DATE';
-- Output ==> 2020-04-01 10:12:57

-- Subtract MicroSeconds to the Specific Date
SELECT ADDDATE(@DateVAl, INTERVAL -150000 MICROSECOND) AS 'Subtract MicroSeconds To a DATE';
-- Output ==> 2020-04-01 10:37:56.850000

Example of DATE_ADD() Function

Add Unit of Time to a Date Using DATE_ADD() Function

In this example, you will learn how to add years, months, weeks, days, hours, minutes, seconds, microseconds, etc. to a DateTime using DATE_ADD() function in MySQL.

Add Unit of Time to a Date Using DATE_ADD() Function
-- --------- ADD Unit of Time To a Specific Date ----------

-- DECLARE a DATETIME Variable
SET @DateVAl = CAST('2020-04-01 10:37:57.100000' AS DATETIME);

-- ADD Days to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 30 DAY) AS 'ADD Days To a DATE';
-- Output ==> 2020-05-01 10:37:57

-- ADD Weeks to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 2 WEEK) AS 'ADD Weeks To a DATE';
-- Output ==> 2020-04-15 10:37:57

-- ADD Months to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 2 MONTH) AS 'ADD Months To a DATE';
-- Output ==> 2020-06-01 10:37:57

-- ADD Quarters to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 2 QUARTER) AS 'ADD Quarters To a DATE';
-- Output ==> 2020-10-01 10:37:57

-- ADD Years to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 2 YEAR) AS 'ADD Years To a DATE';
-- Output ==> 2022-04-01 10:37:57

-- ADD Hours to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 2 HOUR) AS 'ADD Hours To a DATE';
-- Output ==> 2020-04-01 12:37:57

-- ADD Minutes to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 15 MINUTE) AS 'ADD Minutes To a DATE';
-- Output ==> 2020-04-01 10:52:57

-- ADD Seconds to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 1500 SECOND) AS 'ADD Seconds To a DATE';
-- Output ==> 2020-04-01 11:02:57

-- ADD MicroSeconds to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL 150000 MICROSECOND) AS 'ADD MicroSeconds To a DATE';
-- Output ==> 2020-04-01 10:37:57.150000

Subtract Unit of Time Using DATE_ADD() Function

In this example, you will learn how to subtract years, months, weeks, days, hours, minutes, seconds, microseconds, etc. from a DateTime using DATE_ADD() in MySQL.

Subtract Unit of Time Using DATE_ADD() Function
-- --------- Subtract Unit of Time To a Specific Date ----------

-- DECLARE a DATETIME Variable
SET @DateVAl = CAST('2020-04-01 10:37:57.100000' AS DATETIME);

-- Subtract Days to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -30 DAY) AS 'Subtract Days To a DATE';
-- Output ==> 2020-03-02 10:37:57

-- Subtract Weeks to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -2 WEEK) AS 'Subtract Weeks To a DATE';
-- Output ==> 2020-03-18 10:37:57

-- Subtract Months to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -2 MONTH) AS 'Subtract Months To a DATE';
-- Output ==> 2020-02-01 10:37:57

-- Subtract Quarters to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -2 QUARTER) AS 'Subtract Quarters To a DATE';
-- Output ==> 2019-10-01 10:37:57

-- Subtract Years to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -2 YEAR) AS 'Subtract Years To a DATE';
-- Output ==> 2018-04-01 10:37:57

-- Subtract Hours to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -2 HOUR) AS 'Subtract Hours To a DATE';
-- Output ==> 2020-04-01 08:37:57

-- Subtract Minutes to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -15 MINUTE) AS 'Subtract Minutes To a DATE';
-- Output ==> 2020-04-01 10:22:57

-- Subtract Seconds to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -1500 SECOND) AS 'Subtract Seconds To a DATE';
-- Output ==> 2020-04-01 10:12:57

-- Subtract MicroSeconds to the Specific Date
SELECT DATE_ADD(@DateVAl, INTERVAL -150000 MICROSECOND) AS 'Subtract MicroSeconds To a DATE';
-- Output ==> 2020-04-01 10:37:56.850000

Example of DATE_SUB() Function

Subtract Unit of Time using DATE_SUB() Function

In this example, you will learn how to subtract years, months, weeks, days, hours, minutes, seconds, microseconds, etc. from a DateTime using DATE_SUB() in MySQL.

Subtract Unit of Time using DATE_SUB() Function
-- --------- Subtract Unit of Time from a Specific Date ----------

-- DECLARE a DATETIME Variable
SET @DateVAl = CAST('2020-04-01 10:37:57.100000' AS DATETIME);

-- Subtract Days from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 30 DAY) AS 'Subtract Days from a DATE';
-- Output ==> 2020-03-02 10:37:57

-- Subtract Weeks from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 2 WEEK) AS 'Subtract Weeks from a DATE';
-- Output ==> 2020-03-18 10:37:57

-- Subtract Months from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 2 MONTH) AS 'Subtract Months from a DATE';
-- Output ==> 2020-02-01 10:37:57

-- Subtract Quarters from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 2 QUARTER) AS 'Subtract Quarters from a DATE';
-- Output ==> 2019-10-01 10:37:57

-- Subtract Years from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 2 YEAR) AS 'Subtract Years from a DATE';
-- Output ==> 2018-04-01 10:37:57

-- Subtract Hours from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 2 HOUR) AS 'Subtract Hours from a DATE';
-- Output ==> 2020-04-01 08:37:57

-- Subtract Minutes from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 15 MINUTE) AS 'Subtract Minutes from a DATE';
-- Output ==> 2020-04-01 10:22:57

-- Subtract Seconds from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 1500 SECOND) AS 'Subtract Seconds from a DATE';
-- Output ==> 2020-04-01 10:12:57

-- Subtract MicroSeconds from the Specific Date
SELECT DATE_SUB(@DateVAl, INTERVAL 150000 MICROSECOND) AS 'Subtract MicroSeconds from a DATE';
-- Output ==> 2020-04-01 10:37:56.850000

Example of ADD and Subtract Unit of Time From Date Without Using Any Built-in Function

Add Unit of Time to a Specific Date Without Using Any Function

In this example, you will learn how to add years, months, weeks, days, hours, minutes, seconds, microseconds, etc. to a DateTime without using any Built-in function in MySQL. For adding a unit of time to date, we used the Plus(+) operator.

Add Unit of Time to a Specific Date Without Using Any Function
-- --------- ADD Unit of Time To a Specific Date ----------

-- DECLARE a DATETIME Variable
SET @DateVAl = CAST('2020-04-01 10:37:57.100000' AS DATETIME);

-- ADD Days to the Specific Date
SELECT @DateVAl + INTERVAL 30 DAY AS 'ADD Days To a DATE';
-- Output ==> 2020-05-01 10:37:57

-- ADD Weeks to the Specific Date
SELECT @DateVAl + INTERVAL 2 WEEK AS 'ADD Weeks To a DATE';
-- Output ==> 2020-04-15 10:37:57

-- ADD Months to the Specific Date
SELECT @DateVAl + INTERVAL 2 MONTH AS 'ADD Months To a DATE';
-- Output ==> 2020-06-01 10:37:57

-- ADD Quarters to the Specific Date
SELECT @DateVAl + INTERVAL 2 QUARTER AS 'ADD Quarters To a DATE';
-- Output ==> 2020-10-01 10:37:57

-- ADD Years to the Specific Date
SELECT @DateVAl + INTERVAL 2 YEAR AS 'ADD Years To a DATE';
-- Output ==> 2022-04-01 10:37:57

-- ADD Hours to the Specific Date
SELECT @DateVAl + INTERVAL 2 HOUR AS 'ADD Hours To a DATE';
-- Output ==> 2020-04-01 12:37:57

-- ADD Minutes to the Specific Date
SELECT @DateVAl + INTERVAL 15 MINUTE AS 'ADD Minutes To a DATE';
-- Output ==> 2020-04-01 10:52:57

-- ADD Seconds to the Specific Date
SELECT @DateVAl + INTERVAL 1500 SECOND AS 'ADD Seconds To a DATE';
-- Output ==> 2020-04-01 11:02:57

-- ADD MicroSeconds to the Specific Date
SELECT @DateVAl + INTERVAL 150000 MICROSECOND AS 'ADD MicroSeconds To a DATE';
-- Output ==> 2020-04-01 10:37:57.150000

Subtract Unit of Time to a Specific Date Without Using Any Function

In this example, you will learn how to Subtract years, months, weeks, days, hours, minutes, seconds, microseconds, etc. to a DateTime without using any Built-in function in MySQL. For subtracting the unit of time to date, we used the Minus(-) operator.

Subtract Unit of Time to a Specific Date Without Using Any Function

-- --------- Subtract Unit of Time From a Specific Date ----------

-- DECLARE a DATETIME Variable
SET @DateVAl = CAST('2020-04-01 10:37:57.100000' AS DATETIME);

-- Subtract Days From the Specific Date
SELECT @DateVAl - INTERVAL 30 DAY AS 'Subtract Days From a DATE';
-- Output ==> 2020-03-02 10:37:57

-- Subtract Weeks From the Specific Date
SELECT @DateVAl - INTERVAL 2 WEEK AS 'Subtract Weeks From a DATE';
-- Output ==> 2020-03-18 10:37:57

-- Subtract Months From the Specific Date
SELECT @DateVAl - INTERVAL 2 MONTH AS 'Subtract Months From a DATE';
-- Output ==> 2020-02-01 10:37:57

-- Subtract Quarters From the Specific Date
SELECT @DateVAl - INTERVAL 2 QUARTER AS 'Subtract Quarters From a DATE';
-- Output ==> 2019-10-01 10:37:57

-- Subtract Years From the Specific Date
SELECT @DateVAl - INTERVAL 2 YEAR AS 'Subtract Years From a DATE';
-- Output ==> 2018-04-01 10:37:57

-- Subtract Hours From the Specific Date
SELECT @DateVAl - INTERVAL 2 HOUR AS 'Subtract Hours From a DATE';
-- Output ==> 2020-04-01 08:37:57

-- Subtract Minutes From the Specific Date
SELECT @DateVAl - INTERVAL 15 MINUTE AS 'Subtract Minutes From a DATE';
-- Output ==> 2020-04-01 10:22:57

-- Subtract Seconds From the Specific Date
SELECT @DateVAl - INTERVAL 1500 SECOND AS 'Subtract Seconds From a DATE';
-- Output ==> 2020-04-01 10:12:57

-- Subtract MicroSeconds From the Specific Date
SELECT @DateVAl - INTERVAL 150000 MICROSECOND AS 'Subtract MicroSeconds From a DATE';
-- Output ==> 2020-04-01 10:37:56.850000

I hope this article will help you to understand how to add or subtract years, months, weeks, days, hours, minutes, seconds, microseconds to a DateTime in MySQL.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Tagged: convert datetime MySQL SQL functions MySQL Functions Data Conversion MySQL DateTime Functions SQL DateTime Functions

Related Posts


Comments

Recent Posts
Tags