EXTRACT and YEAR/MONTH/DAY in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the EXTRACT function do in MySQL?

The EXTRACT function retrieves a specific part (like year, month, or day) from a date or datetime value.

beginner

How do you extract the year from a date column named 'order_date'?

Use EXTRACT(YEAR FROM order_date) to get the year part from the date.

beginner

Which keyword would you use with EXTRACT to get the month from a date?

Use the keyword MONTH with EXTRACT, like EXTRACT(MONTH FROM date_column).

beginner

Write a query to get the day part from a datetime column named 'created_at'.

SELECT EXTRACT(DAY FROM created_at) AS day_part FROM your_table;

beginner

Can EXTRACT be used on both DATE and DATETIME types?

Yes, EXTRACT works on both DATE and DATETIME types to get parts like year, month, day, hour, minute, and second.

Which of the following extracts the year from a date column 'birth_date'?

A YEAR(birth_date)

B GETYEAR(birth_date)

C EXTRACT(YEAR FROM birth_date)

D DATEPART(YEAR, birth_date)

EXTRACT(YEAR FROM birth_date) is the standard SQL syntax to extract the year part.

What will EXTRACT(MONTH FROM '2024-06-15') return?

A 6

B NULL

C 2024

D 15

It returns 6 because the month part of the date is June (6).

Which part does EXTRACT(DAY FROM '2023-12-01') return?

A 1

B 12

C 2023

D NULL

It returns 1 because the day part of the date is the 1st.

Is EXTRACT valid for extracting hour from a DATETIME value?

A Only for DATE type

B Yes

C No

D Only for TIMESTAMP type

EXTRACT can extract hour, minute, second from DATETIME values.

What does EXTRACT(YEAR FROM NULL) return?

A 0

B Current year

C Error

D NULL

EXTRACT returns NULL when the input date is NULL.

Explain how to use the EXTRACT function to get the year, month, and day from a date in MySQL.

Think about the format EXTRACT(part FROM date).

Describe the difference between extracting parts from DATE and DATETIME types using EXTRACT.

Consider what extra parts DATETIME has compared to DATE.