DATE arithmetic (DATEDIFF, DATE_ADD) in SQL - Time & Space Complexity

Time Complexity: DATE arithmetic (DATEDIFF, DATE_ADD)

O(n)

Understanding Time Complexity

When working with dates in SQL, we often calculate differences or add days. Understanding how the time it takes to do this grows with data size helps us write better queries.

We want to know: how does the work grow when we do date calculations on many rows?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT employee_id, 
       DATEDIFF(day, hire_date, GETDATE()) AS days_worked, 
       DATEADD(day, 30, hire_date) AS trial_end_date
FROM employees;
    

This query calculates how many days each employee has worked and finds a date 30 days after their hire date.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The database processes each row in the employees table once.
• How many times: Once per employee record, so as many times as there are rows.

How Execution Grows With Input

Each row requires a fixed amount of work to calculate date differences and add days.

Input Size (n)	Approx. Operations
10	10 date calculations
100	100 date calculations
1000	1000 date calculations

Pattern observation: The work grows directly with the number of rows. Double the rows, double the calculations.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line with the number of rows processed.

Common Mistake

[X] Wrong: "Date calculations like DATEDIFF or DATEADD take the same time no matter how many rows there are."

[OK] Correct: Each row needs its own calculation, so more rows mean more work and longer time.

Interview Connect

Knowing how date functions scale helps you explain query performance clearly and shows you understand how databases handle data row by row.

Self-Check

"What if we added a WHERE clause to filter only recent hires? How would that affect the time complexity?"

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