DATE arithmetic (DATEDIFF, DATE_ADD) in SQL - Step-by-Step Execution

Concept Flow - DATE arithmetic (DATEDIFF, DATE_ADD)

Start with two dates

↓

Choose operation: DATEDIFF or DATE_ADD

↓

DATEDIFF

↓

Calculate difference

↓

Return number

↓

End

Start with two dates, pick DATEDIFF to find days between or DATE_ADD to add days, then get the result.

Execution Sample

SQL

SELECT DATEDIFF('2024-06-10', '2024-06-01') AS diff_days;
SELECT DATE_ADD('2024-06-01', INTERVAL 5 DAY) AS new_date;

Calculate days between June 1 and June 10, then add 5 days to June 1.

Execution Table

Step	Operation	Input Dates/Values	Calculation	Result
1	DATEDIFF	'2024-06-10', '2024-06-01'	2024-06-10 - 2024-06-01	9
2	DATE_ADD	'2024-06-01', +5 days	Add 5 days to 2024-06-01	2024-06-06
3	End	-	-	-

💡 Both operations complete and return their results.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
date1	'2024-06-10'	'2024-06-10'	'2024-06-10'	'2024-06-10'
date2	'2024-06-01'	'2024-06-01'	'2024-06-01'	'2024-06-01'
diff_days	N/A	9	9	9
new_date	N/A	N/A	'2024-06-06'	'2024-06-06'

Key Moments - 2 Insights

Why does DATEDIFF return 9 when the dates are June 10 and June 1?

DATEDIFF counts full days between the two dates, so from June 1 to June 10 is 9 full days (not including the start date). See execution_table row 1.

What happens if you add 0 days with DATE_ADD?

DATE_ADD returns the original date unchanged because adding zero days means no change. This is shown by the calculation logic in execution_table row 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of DATEDIFF at step 1?

A 1

B 9

C 10

D 0

💡 Hint

Check the 'Result' column in execution_table row 1.

At which step does the new date become '2024-06-06'?

A Step 2

B Step 1

C Step 3

D Never

💡 Hint

Look at the 'Result' column for DATE_ADD operation in execution_table.

If you change the interval in DATE_ADD from 5 days to 10 days, what would the new_date be?

A '2024-06-10'

B '2024-06-15'

C '2024-06-11'

D '2024-06-01'

💡 Hint

Adding 10 days to '2024-06-01' moves the date forward by 10 days.

Concept Snapshot

DATEDIFF(date1, date2) returns days between date1 and date2.
DATE_ADD(date, INTERVAL n DAY) adds n days to date.
DATEDIFF counts full days difference.
DATE_ADD returns a new date shifted by n days.
Use these for simple date calculations in SQL.

Full Transcript

This visual execution shows how SQL date arithmetic works using DATEDIFF and DATE_ADD. First, DATEDIFF calculates the number of full days between two dates, for example between June 1 and June 10, which is 9 days. Then, DATE_ADD adds a number of days to a given date, such as adding 5 days to June 1 to get June 6. The execution table traces each step, showing inputs, calculations, and results. Variables like date1, date2, diff_days, and new_date are tracked to see how values change. Key moments clarify common confusions, like why DATEDIFF returns 9 instead of 10. The quiz tests understanding by asking about results at specific steps and effects of changing intervals. The snapshot summarizes the syntax and behavior for quick reference.

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