DATE_ADD and DATE_SUB in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add 7 days to the current date.

MySQL

SELECT DATE_ADD(CURDATE(), INTERVAL [1] DAY);

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

A7

B1

C30

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong interval unit like MONTH instead of DAY.

Forgetting to specify INTERVAL keyword.

The DATE_ADD function adds a specified interval to a date. Here, adding 7 days to the current date.

2fill in blank

medium

Complete the code to subtract 3 months from the date '2024-06-15'.

MySQL

SELECT DATE_SUB('2024-06-15', INTERVAL [1] MONTH);

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

A1

B12

C6

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using DATE_ADD instead of DATE_SUB for subtraction.

Using DAY instead of MONTH as interval unit.

The DATE_SUB function subtracts a specified interval from a date. Here, subtracting 3 months from June 15, 2024.

3fill in blank

hard

Fix the error in the code to add 10 hours to the current timestamp.

MySQL

SELECT DATE_ADD(NOW(), INTERVAL [1] HOUR);

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

A10

B'10'

Chour

DHOURS

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the number inside quotes making it a string.

Using plural units like HOURS which is invalid.

The interval value must be a number without quotes, and the unit must be uppercase singular like HOUR.

4fill in blank

hard

Fill both blanks to subtract 15 days and add 2 weeks to '2024-01-01'.

MySQL

SELECT DATE_ADD(DATE_SUB('2024-01-01', INTERVAL [1] DAY), INTERVAL [2] WEEK);

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

A15

B10

C2

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up days and weeks values.

Using wrong interval units.

First subtract 15 days, then add 2 weeks to the date '2024-01-01'.

5fill in blank

hard

Fill all three blanks to add 1 year, subtract 6 months, and add 10 days to '2023-03-10'.

MySQL

SELECT DATE_ADD(DATE_SUB(DATE_ADD('2023-03-10', INTERVAL [1] YEAR), INTERVAL [2] MONTH), INTERVAL [3] DAY);

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

A1

B6

C10

D12

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of intervals.

Using incorrect numbers for intervals.

The code adds 1 year, subtracts 6 months, then adds 10 days to the given date.

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