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.

Practice

1. What does the SQL function DATEDIFF('2024-06-10', '2024-06-01') return?

easy

A. 1

B. 10

C. 11

D. 9

Solution

1. Step 1: Understand DATEDIFF parameters

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

2. Step 2: Calculate the difference

   From June 1 to June 10, there are 9 days difference (10 - 1 = 9).

3. Final Answer:

   9 -> Option D

4. Quick Check:

   DATEDIFF('2024-06-10', '2024-06-01') = 9 [OK]

Hint: Subtract dates to get days difference [OK]

Common Mistakes:

• Counting both start and end dates
• Mixing order of dates in DATEDIFF
• Assuming difference is 10 instead of 9

2. Which of the following is the correct syntax to add 5 days to the date '2024-06-01' using DATE_ADD?

easy

A. DATE_ADD('2024-06-01', INTERVAL 5 DAY)

B. DATE_ADD('2024-06-01', 5 DAY)

C. DATE_ADD('2024-06-01', INTERVAL DAY 5)

D. DATE_ADD('2024-06-01', DAY 5 INTERVAL)

Solution

1. Step 1: Recall DATE_ADD syntax

   The correct syntax is DATE_ADD(date, INTERVAL number unit), e.g., INTERVAL 5 DAY.

2. Step 2: Check each option

   Only DATE_ADD('2024-06-01', INTERVAL 5 DAY) follows the correct syntax with INTERVAL 5 DAY.

3. Final Answer:

   DATE_ADD('2024-06-01', INTERVAL 5 DAY) -> Option A

4. Quick Check:

   Correct DATE_ADD syntax uses INTERVAL before number and unit [OK]

Hint: Use INTERVAL keyword before number and unit [OK]

Common Mistakes:

• Omitting INTERVAL keyword
• Swapping number and unit order
• Placing INTERVAL after unit

3. What is the result of this SQL query?

SELECT DATEDIFF('2024-07-01', DATE_ADD('2024-06-25', INTERVAL 3 DAY)) AS diff;

medium

A. 6

B. 3

C. 5

D. 4

Solution

1. Step 1: Calculate DATE_ADD('2024-06-25', INTERVAL 3 DAY)

   Adding 3 days to June 25 gives June 28, 2024.

2. Step 2: Calculate DATEDIFF('2024-07-01', '2024-06-28')

   The difference between July 1 and June 28 is 3 days (June 29, 30, July 1).

3. Final Answer:

   3 -> Option B

4. Quick Check:

   DATEDIFF('2024-07-01', '2024-06-28') = 3 [OK]

Hint: Calculate DATE_ADD first, then DATEDIFF [OK]

Common Mistakes:

• Counting days incorrectly between dates
• Forgetting to add days before DATEDIFF
• Assuming DATEDIFF counts inclusive days

4. Identify the error in this SQL statement:

SELECT DATE_ADD('2024-06-01', 5 DAY);

medium

A. DATE_ADD cannot add days

B. Wrong date format

C. Missing INTERVAL keyword before 5 DAY

D. Extra semicolon inside function

Solution

1. Step 1: Review DATE_ADD syntax

   DATE_ADD requires INTERVAL keyword before the number and unit.

2. Step 2: Check the given statement

   The statement misses INTERVAL before '5 DAY', causing syntax error.

3. Final Answer:

   Missing INTERVAL keyword before 5 DAY -> Option C

4. Quick Check:

   DATE_ADD needs INTERVAL before number and unit [OK]

Hint: Always use INTERVAL before number and unit [OK]

Common Mistakes:

• Omitting INTERVAL keyword
• Using wrong date format
• Adding semicolon inside function

5. You want to find how many days are left until a deadline 10 days after '2024-06-15'. Which SQL query correctly calculates the days left from '2024-06-20'?

hard

A. SELECT DATEDIFF(DATE_ADD('2024-06-15', INTERVAL 10 DAY), '2024-06-20') AS days_left;

B. SELECT DATEDIFF('2024-06-20', DATE_ADD('2024-06-15', INTERVAL 10 DAY)) AS days_left;

C. SELECT DATEDIFF('2024-06-15', DATE_ADD('2024-06-20', INTERVAL 10 DAY)) AS days_left;

D. SELECT DATEDIFF('2024-06-20', '2024-06-15' + INTERVAL 10 DAY) AS days_left;

Solution

1. Step 1: Calculate deadline date

   Deadline is 10 days after June 15, so DATE_ADD('2024-06-15', INTERVAL 10 DAY) = June 25.

2. Step 2: Calculate days left from June 20 to deadline

   DATEDIFF(deadline, current_date) = DATEDIFF('2024-06-25', '2024-06-20') = 5 days left.

3. Final Answer:

   SELECT DATEDIFF(DATE_ADD('2024-06-15', INTERVAL 10 DAY), '2024-06-20') AS days_left; -> Option A

4. Quick Check:

   Days left = deadline - current date = 5 [OK]

Hint: Subtract current date from deadline date [OK]

Common Mistakes:

• Swapping date order in DATEDIFF
• Adding interval to wrong date
• Using wrong syntax for INTERVAL