DATE arithmetic (DATEDIFF, DATE_ADD) in SQL - Mini Project: Build & Apply

Calculate Employee Contract Durations and Renewal Dates

📖 Scenario: You work in the HR department of a company. You have a table of employees with their contract start and end dates. You want to calculate how many days each employee's contract lasts and find the date when their contract should be renewed by adding 30 days to the end date.

🎯 Goal: Create SQL queries that calculate the duration of each employee's contract in days using DATEDIFF and find the renewal date by adding 30 days to the contract end date using DATE_ADD.

📋 What You'll Learn

Create a table called employees with columns id, name, contract_start, and contract_end.

Insert exactly three employees with specified contract start and end dates.

Write a query to calculate the contract duration in days using DATEDIFF.

Write a query to calculate the renewal date by adding 30 days to contract_end using DATE_ADD.

💡 Why This Matters

🌍 Real World

HR departments often need to track contract durations and renewal dates to manage employee agreements efficiently.

💼 Career

Knowing how to use date arithmetic functions like DATEDIFF and DATE_ADD is essential for database analysts and developers working with time-based data.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (varchar), contract_start (date), and contract_end (date). Then insert these three employees exactly: (1, 'Alice', '2023-01-01', '2023-12-31'), (2, 'Bob', '2023-03-15', '2023-09-14'), and (3, 'Charlie', '2023-06-01', '2024-05-31').

SQL

-- Create the employees table and insert the three employees
-- Your code here

Hint

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

2

Add a helper column alias for contract duration calculation

Write a SELECT query from employees that includes all columns and add a new column alias called duration_days which will hold the number of days between contract_start and contract_end. Use DATEDIFF(contract_end, contract_start) for this calculation.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  contract_start DATE,
  contract_end DATE
);

INSERT INTO employees (id, name, contract_start, contract_end) VALUES
(1, 'Alice', '2023-01-01', '2023-12-31'),
(2, 'Bob', '2023-03-15', '2023-09-14'),
(3, 'Charlie', '2023-06-01', '2024-05-31');

-- Select all columns and add duration_days using DATEDIFF
-- Your code here

Hint

Use Datediff(contract_end, contract_start) and alias it as duration_days.

3

Calculate contract renewal date by adding 30 days

Extend the previous SELECT query to add a new column alias called renewal_date which adds 30 days to the contract_end date. Use DATE_ADD(contract_end, INTERVAL 30 DAY) for this calculation.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  contract_start DATE,
  contract_end DATE
);

INSERT INTO employees (id, name, contract_start, contract_end) VALUES
(1, 'Alice', '2023-01-01', '2023-12-31'),
(2, 'Bob', '2023-03-15', '2023-09-14'),
(3, 'Charlie', '2023-06-01', '2024-05-31');

SELECT id, name, contract_start, contract_end, DATEDIFF(contract_end, contract_start) AS duration_days,
-- Add renewal_date column here using DATE_ADD
-- Your code here
FROM employees;

Hint

Use DATE_ADD(contract_end, INTERVAL 30 DAY) and alias it as renewal_date.

4

Complete the query with ordering by renewal date

Modify the SELECT query to order the results by renewal_date in ascending order.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  contract_start DATE,
  contract_end DATE
);

INSERT INTO employees (id, name, contract_start, contract_end) VALUES
(1, 'Alice', '2023-01-01', '2023-12-31'),
(2, 'Bob', '2023-03-15', '2023-09-14'),
(3, 'Charlie', '2023-06-01', '2024-05-31');

SELECT id, name, contract_start, contract_end, DATEDIFF(contract_end, contract_start) AS duration_days,
       DATE_ADD(contract_end, INTERVAL 30 DAY) AS renewal_date
FROM employees
-- Add ORDER BY renewal_date ascending
-- Your code here

Hint

Add ORDER BY renewal_date ASC at the end of the query.

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