DATE_ADD and DATE_SUB in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the MySQL function DATE_ADD() do?

DATE_ADD() adds a specified time interval to a date and returns the new date.

beginner

How does DATE_SUB() differ from DATE_ADD()?

DATE_SUB() subtracts a specified time interval from a date, while DATE_ADD() adds it.

beginner

Write a simple example of DATE_ADD() to add 5 days to '2024-06-01'.

SELECT DATE_ADD('2024-06-01', INTERVAL 5 DAY); -- Result: '2024-06-06'

intermediate

What types of intervals can you use with DATE_ADD() and DATE_SUB()?

You can use intervals like DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, and more.

beginner

Why would you use DATE_SUB() in a database query?

To find a date before a given date, such as finding records older than a certain number of days.

What will this query return? SELECT DATE_ADD('2024-06-10', INTERVAL 3 DAY);

A '2024-06-03'

B '2024-06-07'

C '2024-06-13'

D '2024-07-10'

Adding 3 days to June 10, 2024 results in June 13, 2024.

Which function subtracts time from a date in MySQL?

A DATE_SUB()

B DATE_ADD()

C DATE_DIFF()

D DATE_REMOVE()

DATE_SUB() subtracts a specified interval from a date.

What interval keyword would you use to add 2 months using DATE_ADD()?

A DAY

B MONTH

C YEAR

D HOUR

MONTH is the correct interval keyword to add months.

If you want to find the date 10 days before today, which query is correct?

A SELECT DATE_SUB(CURDATE(), INTERVAL -10 DAY);

B SELECT DATE_ADD(CURDATE(), INTERVAL 10 DAY);

C SELECT DATE_ADD(CURDATE(), INTERVAL -10 DAY);

D SELECT DATE_SUB(CURDATE(), INTERVAL 10 DAY);

DATE_SUB with 10 days subtracts 10 days from today.

Can DATE_ADD() and DATE_SUB() be used with time intervals like HOUR and MINUTE?

A Yes, they support various time units including HOUR and MINUTE.

B No, only DAY and MONTH are supported.

C Only DATE_ADD() supports time intervals.

D Only DATE_SUB() supports time intervals.

Both functions support many interval types including HOUR and MINUTE.

Explain how DATE_ADD() and DATE_SUB() work in MySQL and give an example of each.

Think about adding or subtracting time intervals from a date.

Describe a real-life situation where you might use DATE_SUB() in a database query.

Consider situations like finding expired items or past events.