DATE_ADD and DATE_SUB in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this DATE_ADD query?

Given the table events with a column event_date of type DATE, what will be the result of this query?

SELECT DATE_ADD('2024-06-15', INTERVAL 10 DAY) AS new_date;

MySQL

SELECT DATE_ADD('2024-06-15', INTERVAL 10 DAY) AS new_date;

A 2024-06-05

B 2024-06-25

C 2024-07-15

D 2024-06-16

💡 Hint

Think about adding 10 days to June 15, 2024.

DATE_ADD adds the specified interval to the date. Adding 10 days to June 15, 2024 results in June 25, 2024.

❓ query_result

intermediate

What does this DATE_SUB query return?

Consider the query:

SELECT DATE_SUB('2024-01-01', INTERVAL 1 MONTH) AS previous_month;

What is the output?

MySQL

SELECT DATE_SUB('2024-01-01', INTERVAL 1 MONTH) AS previous_month;

A 2023-12-31

B 2023-11-01

C 2023-12-30

D 2023-12-01

💡 Hint

Subtracting one month from January 1, 2024 goes to December 2023. Check the day part carefully.

DATE_SUB subtracts the interval. Subtracting 1 month from January 1, 2024 results in December 1, 2023.

📝 Syntax

advanced

Which query correctly adds 2 hours to a datetime value?

You want to add 2 hours to the datetime '2024-06-15 10:00:00'. Which of these queries is syntactically correct and returns the expected result?

A SELECT DATE_ADD('2024-06-15 10:00:00', INTERVAL 2 HOUR);

B SELECT DATE_ADD('2024-06-15 10:00:00', 2 HOURS);

C SELECT DATE_ADD('2024-06-15 10:00:00', INTERVAL '2' HOUR);

D SELECT DATE_ADD('2024-06-15 10:00:00', INTERVAL 2 HOURS);

💡 Hint

Check the syntax for INTERVAL and the unit name.

The correct syntax uses INTERVAL followed by a number and a singular unit name without quotes. Option A is correct. Options A, B, and D have syntax errors.

❓ optimization

advanced

Optimizing date subtraction in a WHERE clause

You want to select all records from orders where order_date is within the last 30 days. Which query is the most efficient?

A SELECT * FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

B SELECT * FROM orders WHERE DATE_SUB(order_date, INTERVAL 30 DAY) >= CURDATE();

C SELECT * FROM orders WHERE order_date >= CURDATE() - INTERVAL 30 DAY;

D SELECT * FROM orders WHERE order_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE();

💡 Hint

Think about how indexes work with functions on columns.

Applying functions on columns (like DATE_SUB(order_date, ...)) prevents index use. Option A compares the column directly to a calculated constant, allowing index use and better performance.

🧠 Conceptual

expert

What is the result of this complex date arithmetic?

Consider this query:

SELECT DATE_ADD(DATE_SUB('2024-03-31', INTERVAL 1 MONTH), INTERVAL 1 MONTH) AS result_date;

What is the value of result_date?

MySQL

SELECT DATE_ADD(DATE_SUB('2024-03-31', INTERVAL 1 MONTH), INTERVAL 1 MONTH) AS result_date;

A 2024-03-31

B 2024-02-29

C 2024-03-29

D 2024-03-01

💡 Hint

Subtracting one month from March 31, 2024, then adding one month back may not return the original date.

DATE_SUB('2024-03-31', INTERVAL 1 MONTH) results in '2024-02-29' (leap year). Adding one month to '2024-02-29' results in '2024-03-29'.

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