DATE_ADD and DATE_SUB in MySQL - Mini Project: Build & Apply

Using DATE_ADD and DATE_SUB in MySQL

📖 Scenario: You work in a small library database. You want to calculate due dates for borrowed books and check overdue dates by adding or subtracting days from the borrow date.

🎯 Goal: Build SQL queries that use DATE_ADD and DATE_SUB functions to calculate new dates based on a borrow date.

📋 What You'll Learn

Create a table called borrow_records with columns id (integer), borrow_date (date).

Insert exactly two rows with borrow_date values '2024-06-01' and '2024-06-15'.

Write a query using DATE_ADD to find the due date by adding 14 days to borrow_date.

Write a query using DATE_SUB to find the reminder date by subtracting 3 days from the due date.

💡 Why This Matters

🌍 Real World

Libraries, rental services, and any system that tracks deadlines use date calculations to manage due dates and reminders.

💼 Career

Database developers and analysts often write queries with date functions to automate scheduling and notifications.

1

Create the borrow_records table and insert data

Create a table called borrow_records with columns id as integer and borrow_date as date. Insert two rows with id values 1 and 2, and borrow_date values '2024-06-01' and '2024-06-15' respectively.

MySQL

-- Create table borrow_records and insert two rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a query to calculate due dates using DATE_ADD

Write a SELECT query that retrieves id, borrow_date, and a new column due_date which is borrow_date plus 14 days using DATE_ADD.

MySQL

CREATE TABLE borrow_records (
  id INT,
  borrow_date DATE
);

INSERT INTO borrow_records (id, borrow_date) VALUES
(1, '2024-06-01'),
(2, '2024-06-15');

-- Write SELECT query with DATE_ADD to get due_date
-- Your code here

Hint

Use DATE_ADD(borrow_date, INTERVAL 14 DAY) to add 14 days.

3

Add a query to calculate reminder dates using DATE_SUB

Write a SELECT query that retrieves id, borrow_date, due_date (borrow_date plus 14 days), and a new column reminder_date which is due_date minus 3 days using DATE_SUB.

MySQL

CREATE TABLE borrow_records (
  id INT,
  borrow_date DATE
);

INSERT INTO borrow_records (id, borrow_date) VALUES
(1, '2024-06-01'),
(2, '2024-06-15');

-- Write SELECT query with DATE_ADD and DATE_SUB to get due_date and reminder_date
-- Your code here

Hint

Use DATE_SUB on the result of DATE_ADD to subtract 3 days.

4

Complete the query with ORDER BY to sort by due_date

Add an ORDER BY clause to the previous SELECT query to sort the results by due_date in ascending order.

MySQL

CREATE TABLE borrow_records (
  id INT,
  borrow_date DATE
);

INSERT INTO borrow_records (id, borrow_date) VALUES
(1, '2024-06-01'),
(2, '2024-06-15');

SELECT id, borrow_date,
       DATE_ADD(borrow_date, INTERVAL 14 DAY) AS due_date,
       DATE_SUB(DATE_ADD(borrow_date, INTERVAL 14 DAY), INTERVAL 3 DAY) AS reminder_date
FROM borrow_records
-- Add ORDER BY due_date ascending
-- Your code here

Hint

Use ORDER BY due_date ASC to sort dates from earliest to latest.

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