DATE arithmetic (DATEDIFF, DATE_ADD) in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to find the number of days between two dates.

SQL

SELECT DATEDIFF([1], '2024-01-01') AS days_diff;

Drag options to blanks, or click blank then click option'

A'2023-12-31'

B'2024-01-10'

C'2024-02-01'

D'2024-01-01'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the dates in the wrong order, which changes the sign of the result.

Using a date format not recognized by SQL.

The DATEDIFF function calculates the difference in days between two dates. Here, we find days between '2024-01-10' and '2024-01-01', which is 9 days.

2fill in blank

medium

Complete the code to add 7 days to a given date.

SQL

SELECT DATE_ADD('2024-03-01', INTERVAL [1] DAY) AS new_date;

Drag options to blanks, or click blank then click option'

A1

B0

C7

D30

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong interval unit like MONTH instead of DAY.

Adding zero days which returns the same date.

The DATE_ADD function adds a specified interval to a date. Adding 7 days to '2024-03-01' results in '2024-03-08'.

3fill in blank

hard

Fix the error in the code to subtract 10 days from a date.

SQL

SELECT DATE_ADD('2024-05-15', INTERVAL [1] DAY) AS new_date;

Drag options to blanks, or click blank then click option'

A-10

B10

CDAY -10

DINTERVAL -10 DAY

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the negative sign outside the INTERVAL keyword.

Using incorrect syntax like 'DAY -10'.

To subtract days using DATE_ADD, use a negative number in the INTERVAL. So, INTERVAL -10 DAY subtracts 10 days.

4fill in blank

hard

Fill both blanks to calculate the difference in days between two dates stored in columns.

SQL

SELECT DATEDIFF([1], [2]) AS days_between FROM orders;

Drag options to blanks, or click blank then click option'

Aorder_date

Bdelivery_date

C'2024-01-01'

D'2024-12-31'

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the columns and getting negative results.

Using fixed dates instead of column names.

To find how many days passed from order to delivery, subtract order_date from delivery_date. So, DATEDIFF(delivery_date, order_date).

5fill in blank

hard

Fill all three blanks to create a dictionary of event names and their day differences from today.

SQL

SELECT JSON_OBJECT([1], DATEDIFF([2], CURRENT_DATE)) AS days_diff FROM events WHERE [3] > CURRENT_DATE;

Drag options to blanks, or click blank then click option'

Aevent_date

Devent_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same column for key and filter incorrectly.

Not filtering future dates properly.

This query selects event names as keys and calculates days until the event by subtracting current date from event_date. It filters future events only.

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