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

Need a 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

Need a 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

Need a 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

Need a hint?

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