DATE_ADD and DATE_SUB in MySQL - Step-by-Step Execution

Concept Flow - DATE_ADD and DATE_SUB

Start with a date

↓

Choose operation: Add or Subtract

↓

DATE_ADD

↓

Add interval

↓

Return new date

↓

End

Start with a date, choose to add or subtract a time interval, then get the new date as result.

Execution Sample

MySQL

SELECT DATE_ADD('2024-06-01', INTERVAL 5 DAY) AS added_date;
SELECT DATE_SUB('2024-06-01', INTERVAL 3 DAY) AS subtracted_date;

Add 5 days and subtract 3 days from the date '2024-06-01'.

Execution Table

Step	Function	Input Date	Interval	Operation	Result
1	DATE_ADD	2024-06-01	5 DAY	Add 5 days	2024-06-06
2	DATE_SUB	2024-06-01	3 DAY	Subtract 3 days	2024-05-29
3	END	-	-	-	Execution complete

💡 All intervals applied, results returned, execution ends.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Input Date	2024-06-01	2024-06-01	2024-06-01	2024-06-01
Added Date	N/A	2024-06-06	2024-06-06	2024-06-06
Subtracted Date	N/A	N/A	2024-05-29	2024-05-29

Key Moments - 2 Insights

Why does DATE_ADD('2024-06-01', INTERVAL 5 DAY) return '2024-06-06'?

Because it adds 5 days to the original date, moving from June 1 to June 6 as shown in execution_table row 1.

Why does DATE_SUB('2024-06-01', INTERVAL 3 DAY) return '2024-05-29'?

Because it subtracts 3 days from June 1, going back to May 29, as shown in execution_table row 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of DATE_ADD at step 1?

A 2024-06-06

B 2024-06-05

C 2024-05-29

D 2024-06-01

💡 Hint

Check the 'Result' column in execution_table row 1.

At which step does the date become '2024-05-29'?

A Step 1

B Step 2

C Step 3

D Never

💡 Hint

Look at the 'Result' column in execution_table row 2.

If we change the interval in DATE_SUB to 1 DAY, what would be the new result?

A 2024-06-01

B 2024-05-30

C 2024-05-31

D 2024-05-29

💡 Hint

Subtracting 1 day from 2024-06-01 moves the date back by one day.

Concept Snapshot

DATE_ADD(date, INTERVAL n unit) adds time to a date.
DATE_SUB(date, INTERVAL n unit) subtracts time from a date.
Units can be DAY, MONTH, YEAR, etc.
Returns a new date shifted by the interval.
Useful for date calculations in SQL.

Full Transcript

This visual execution shows how DATE_ADD and DATE_SUB work in MySQL. Starting with a date, you choose to add or subtract a time interval. For example, adding 5 days to '2024-06-01' results in '2024-06-06'. Subtracting 3 days from the same date results in '2024-05-29'. The execution table tracks each step and the resulting dates. Variables like input date and results are tracked to show changes. Key moments clarify why the dates change as they do. Quizzes test understanding by asking about specific steps and results. The snapshot summarizes the syntax and behavior for quick reference.

Practice

1. What does the MySQL function DATE_ADD(date, INTERVAL expr unit) do?

easy

A. Returns the current date and time

B. Subtracts a specified time interval from a date

C. Adds a specified time interval to a date

D. Formats a date into a string

Solution

1. Step 1: Understand the function purpose

   The function DATE_ADD is designed to add a time interval to a given date value.

2. Step 2: Compare options with function behavior

   'Adds a specified time interval to a date' correctly states that it adds a specified time interval. 'Subtracts a specified time interval from a date' describes DATE_SUB, not DATE_ADD. The options for returning the current date and time and formatting a date into a string describe unrelated functions.

3. Final Answer:

   Adds a specified time interval to a date -> Option C

4. Quick Check:

   DATE_ADD adds time interval [OK]

Hint: DATE_ADD adds time; DATE_SUB subtracts time [OK]

Common Mistakes:

• Confusing DATE_ADD with DATE_SUB
• Thinking DATE_ADD formats dates
• Assuming DATE_ADD returns current date

2. Which of the following is the correct syntax using DATE_SUB to subtract 10 days from the date '2024-06-15' in MySQL?

easy

A. DATE_ADD('2024-06-15', INTERVAL -10 DAY)

B. DATE_SUB('2024-06-15', INTERVAL 10 DAY)

C. DATE_SUB('2024-06-15', 10 DAY)

D. DATE_ADD('2024-06-15', 10 DAYS)

Solution

1. Step 1: Check correct function and syntax for subtraction

   To subtract days, use DATE_SUB with INTERVAL keyword and unit DAY.

2. Step 2: Validate each option's syntax

   DATE_SUB('2024-06-15', INTERVAL 10 DAY) uses correct syntax: DATE_SUB('date', INTERVAL 10 DAY). DATE_ADD('2024-06-15', INTERVAL -10 DAY) uses DATE_ADD with negative interval, which is not recommended. DATE_SUB('2024-06-15', 10 DAY) misses INTERVAL keyword. DATE_ADD('2024-06-15', 10 DAYS) misses the INTERVAL keyword and uses DATE_ADD which adds instead of subtracts.

3. Final Answer:

   DATE_SUB('2024-06-15', INTERVAL 10 DAY) -> Option B

4. Quick Check:

   Correct syntax uses DATE_SUB with INTERVAL [OK]

Hint: Use DATE_SUB with INTERVAL and unit DAY to subtract days [OK]

Common Mistakes:

• Omitting INTERVAL keyword
• Using plural unit like DAYS instead of DAY
• Using DATE_ADD with negative interval

3. What is the result of this query?

SELECT DATE_ADD('2024-06-10', INTERVAL 3 MONTH) AS new_date;

medium

A. 2024-03-10

B. 2024-06-10

C. 2024-06-13

D. 2024-09-10

Solution

1. Step 1: Understand DATE_ADD with MONTH interval

   The query adds 3 months to the date '2024-06-10'. Adding 3 months to June 10, 2024, results in September 10, 2024.

2. Step 2: Match the calculated date with options

   2024-09-10 shows '2024-09-10', which matches the expected result. Other options are incorrect dates or no change.

3. Final Answer:

   2024-09-10 -> Option D

4. Quick Check:

   June 10 + 3 months = September 10 [OK]

Hint: Add months carefully; day stays same if possible [OK]

Common Mistakes:

• Adding days instead of months
• Confusing month addition with subtraction
• Expecting day to change unexpectedly

4. Identify the error in this MySQL query:

SELECT DATE_SUB('2024-06-20', 5 DAY);

medium

A. Missing INTERVAL keyword before 5 DAY

B. Incorrect function name, should be DATE_ADD

C. Date format is invalid

D. DAY should be plural DAYS

Solution

1. Step 1: Review DATE_SUB syntax

   The correct syntax requires the INTERVAL keyword before the number and unit, like DATE_SUB(date, INTERVAL expr unit).

2. Step 2: Identify the missing keyword

   The query uses DATE_SUB('2024-06-20', 5 DAY) missing INTERVAL, causing syntax error.

3. Final Answer:

   Missing INTERVAL keyword before 5 DAY -> Option A

4. Quick Check:

   DATE_SUB needs INTERVAL keyword [OK]

Hint: Always include INTERVAL keyword with DATE_ADD/SUB [OK]

Common Mistakes:

• Omitting INTERVAL keyword
• Using plural unit DAYS instead of DAY
• Confusing DATE_ADD and DATE_SUB

5. You want to find the date 2 weeks before the last day of the current month in MySQL using DATE_SUB. Which query correctly achieves this?

hard

A. SELECT DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result;

B. SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL -2 WEEK) AS result;

C. SELECT DATE_SUB(CURDATE(), INTERVAL 2 WEEK) AS result;

D. SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result;

Solution

1. Step 1: Understand the requirement

   We need the date 2 weeks before the last day of the current month. LAST_DAY(CURDATE()) gives the last day of this month.

2. Step 2: Apply correct date arithmetic

   Subtracting 2 weeks from that date is done with DATE_SUB(..., INTERVAL 2 WEEK). SELECT DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result; uses this correctly. SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL -2 WEEK) AS result; uses DATE_ADD instead of DATE_SUB. SELECT DATE_SUB(CURDATE(), INTERVAL 2 WEEK) AS result; subtracts from current date, not last day. SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result; adds 2 weeks, which is incorrect.

3. Final Answer:

   SELECT DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 2 WEEK) AS result; -> Option A

4. Quick Check:

   Subtract 2 weeks from last day using DATE_SUB [OK]

Hint: Use LAST_DAY with DATE_SUB for relative month-end dates [OK]

Common Mistakes:

• Subtracting from current date instead of last day
• Using DATE_ADD with positive interval instead of subtraction
• Confusing weeks with days in interval