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.