DATE arithmetic (DATEDIFF, DATE_ADD) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate days difference between two dates

Given the table Events with a column event_date, what is the output of this query?

SELECT DATEDIFF('2024-06-15', '2024-06-10') AS days_diff;

SQL

SELECT DATEDIFF('2024-06-15', '2024-06-10') AS days_diff;

A [{"days_diff": 10}]

B [{"days_diff": 5}]

C [{"days_diff": 0}]

D [{"days_diff": -5}]

💡 Hint

DATEDIFF returns the number of days between the first date and the second date.

DATEDIFF('2024-06-15', '2024-06-10') calculates how many days from June 10 to June 15, which is 5 days.

❓ query_result

intermediate

Add days to a date using DATE_ADD

What is the result of this query?

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

SQL

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

A [{"new_date": "2024-06-10"}]

B [{"new_date": "2024-05-22"}]

C [{"new_date": "2024-06-21"}]

D [{"new_date": "2024-06-11"}]

💡 Hint

DATE_ADD adds the specified interval to the given date.

Adding 10 days to June 1, 2024 results in June 11, 2024.

📝 Syntax

advanced

Identify the syntax error in DATE_ADD usage

Which option contains a syntax error in using DATE_ADD to add 3 months to '2024-01-01'?

A SELECT DATE_ADD('2024-01-01', INTERVAL 3MONTH);

B SELECT DATE_ADD('2024-01-01', INTERVAL 3 MONTH);

C SELECT DATE_ADD('2024-01-01', INTERVAL '3' MONTH);

D SELECT DATE_ADD('2024-01-01', INTERVAL 3 MONTH) AS new_date;

💡 Hint

Check spacing between the number and the unit in INTERVAL.

Option A misses a space between 3 and MONTH, causing a syntax error.

❓ query_result

advanced

Calculate difference in days with reversed dates

What is the output of this query?

SELECT DATEDIFF('2024-06-01', '2024-06-10') AS days_diff;

SQL

SELECT DATEDIFF('2024-06-01', '2024-06-10') AS days_diff;

A [{"days_diff": -9}]

B [{"days_diff": 9}]

C [{"days_diff": 0}]

D [{"days_diff": 10}]

💡 Hint

DATEDIFF returns positive if first date is later, negative if earlier.

Since June 1 is before June 10, the difference is negative 9 days.

🧠 Conceptual

expert

Understanding DATE_ADD with mixed intervals

Which option correctly adds 1 year, 2 months, and 10 days to '2023-01-01' using DATE_ADD?

A SELECT DATE_ADD('2023-01-01', INTERVAL 1 YEAR + 2 MONTH + 10 DAY) AS new_date;

B SELECT DATE_ADD('2023-01-01', INTERVAL '1 YEAR 2 MONTH 10 DAY');

C SELECT DATE_ADD(DATE_ADD(DATE_ADD('2023-01-01', INTERVAL 1 YEAR), INTERVAL 2 MONTH), INTERVAL 10 DAY) AS new_date;

D SELECT DATE_ADD('2023-01-01', INTERVAL 1 YEAR), DATE_ADD('2023-01-01', INTERVAL 2 MONTH), DATE_ADD('2023-01-01', INTERVAL 10 DAY);

💡 Hint

DATE_ADD only accepts one interval at a time; chaining is needed for multiple intervals.

Option C chains DATE_ADD calls to add each interval step by step, which is valid.