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'.