DATE arithmetic (DATEDIFF, DATE_ADD) in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL function DATEDIFF do?

DATEDIFF calculates the number of days between two dates. It tells you how many days separate one date from another.

beginner

How do you add 10 days to a date in SQL?

You use DATE_ADD with an interval of 10 days. For example: DATE_ADD('2024-01-01', INTERVAL 10 DAY) gives you the date 10 days after January 1, 2024.

beginner

What is the difference between DATEDIFF and DATE_ADD?

DATEDIFF finds the number of days between two dates. DATE_ADD changes a date by adding a certain amount of time (days, months, etc.) to it.

intermediate

Write a SQL query to find how many days have passed since '2024-01-01' until today.

SELECT DATEDIFF(CURRENT_DATE, '2024-01-01');

This returns the number of days from January 1, 2024, to today.

beginner

How can you add 3 months to a date using SQL?

Use DATE_ADD with INTERVAL 3 MONTH. Example: DATE_ADD('2024-01-01', INTERVAL 3 MONTH) gives April 1, 2024.

What does DATEDIFF('2024-01-10', '2024-01-01') return?

A 1

B 9

C 11

D 10

DATEDIFF counts the days between the two dates, excluding the start date, so it returns 9.

Which SQL function adds a time interval to a date?

A DATE_ADD

B DATEDIFF

C DATE_SUB

D NOW

DATE_ADD adds a specified interval (days, months, etc.) to a date.

How do you subtract 5 days from a date in SQL?

A DATE_ADD(date, INTERVAL 5 DAY)

B DATEDIFF(date, 5)

C DATE_SUB(date, INTERVAL 5 DAY)

D SUBDATE(date, 5)

DATE_SUB subtracts a time interval from a date.

What will DATE_ADD('2024-02-28', INTERVAL 1 DAY) return?

A '2024-02-29'

B '2024-03-01'

C '2024-02-28'

D Error

2024 is a leap year, so adding 1 day to Feb 28 gives Feb 29.

If you want to find the number of days between two dates, which function do you use?

A DATE_ADD

B NOW

C CURDATE

D DATEDIFF

DATEDIFF returns the number of days between two dates.

Explain how to calculate the number of days between two dates in SQL and give an example.

Think about how to find the difference in days between '2024-01-01' and '2024-01-10'.

Describe how to add a time interval (like days or months) to a date in SQL and provide a sample query.

Consider adding 10 days or 3 months to a specific date.