YEAR, MONTH, DAY extraction in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL function YEAR() do?

The YEAR() function extracts the year part from a date or datetime value.

beginner

How do you extract the month from a date in SQL?

Use the MONTH() function to get the month number (1-12) from a date or datetime value.

beginner

What is the purpose of the DAY() function in SQL?

The DAY() function extracts the day of the month (1-31) from a date or datetime value.

intermediate

Write a SQL query to get the year, month, and day from a column named order_date.

SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, DAY(order_date) AS day FROM your_table;

beginner

Why is extracting year, month, and day useful in databases?

It helps to filter, group, or analyze data based on parts of dates, like finding sales in a specific month or year.

Which SQL function extracts the year from a date?

A DAY()

B MONTH()

C DATEPART()

D YEAR()

The YEAR() function extracts the year part from a date.

What does MONTH('2024-06-15') return?

A 6

B 15

C 2024

D NULL

MONTH() returns the month number, which is 6 for June.

If you want to get the day from a date, which function do you use?

A MONTH()

B DAY()

C YEAR()

D DATE()

DAY() extracts the day of the month from a date.

Which SQL query correctly extracts year, month, and day from birth_date?

A SELECT YEAR(birth_date), MONTH(birth_date), DAY(birth_date) FROM table;

B SELECT DATE(birth_date) FROM table;

C SELECT EXTRACT(YEAR, birth_date) FROM table;

D SELECT GETDATE(birth_date) FROM table;

Option A uses the correct functions YEAR(), MONTH(), and DAY() to extract parts of the date.

What is the output of DAY('2024-06-01')?

A 2024

B 6

C 1

D NULL

DAY() returns the day part, which is 1 for the first day of the month.

Explain how to extract the year, month, and day from a date column in SQL and why it might be useful.

Think about how you can break a date into smaller parts to analyze data.

Write a simple SQL query to select the year, month, and day from a column named created_at in a table called users.

Use the YEAR(), MONTH(), and DAY() functions in the SELECT clause.