YEAR, MONTH, DAY extraction in SQL - Step-by-Step Execution

Concept Flow - YEAR, MONTH, DAY extraction

Start with a date value

↓

Apply YEAR() function

→Extract year part

↓

Apply MONTH() function

→Extract month part

↓

Apply DAY() function

→Extract day part

↓

Use extracted parts in query or output

Extract year, month, and day parts from a date using SQL functions YEAR(), MONTH(), and DAY() step-by-step.

Execution Sample

SQL

SELECT
  YEAR('2024-06-15') AS year_part,
  MONTH('2024-06-15') AS month_part,
  DAY('2024-06-15') AS day_part;

Extracts year, month, and day from the date '2024-06-15'.

Execution Table

Step	Function Applied	Input Date	Extracted Value	Output Column
1	YEAR()	'2024-06-15'	2024	year_part
2	MONTH()	'2024-06-15'	6	month_part
3	DAY()	'2024-06-15'	15	day_part
4	Final Output	-	2024, 6, 15	All columns

💡 All parts extracted; query returns one row with year=2024, month=6, day=15.

Variable Tracker

Variable	Start	After YEAR()	After MONTH()	After DAY()	Final
year_part	NULL	2024	2024	2024	2024
month_part	NULL	NULL	6	6	6
day_part	NULL	NULL	NULL	15	15

Key Moments - 3 Insights

Why does YEAR() return 2024 instead of the full date?

YEAR() extracts only the year part from the date string, ignoring month and day, as shown in execution_table step 1.

Can MONTH() return a value greater than 12?

No, MONTH() returns the month number (1-12) from the date, as shown in execution_table step 2 where it returns 6.

What happens if the input date is NULL?

All extraction functions return NULL if input is NULL, so no year, month, or day is extracted.

Visual Quiz - 3 Questions

Look at the execution_table, what value does DAY() extract at step 3?

A 6

B 15

C 2024

D NULL

💡 Hint

Check the 'Extracted Value' column at step 3 in execution_table.

At which step does the query extract the month from the date?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look for the row where 'Function Applied' is MONTH() in execution_table.

If the input date was '2023-12-31', what would YEAR() return?

A 31

B 12

C 2023

D NULL

💡 Hint

YEAR() extracts the year part; see variable_tracker for how year_part changes.

Concept Snapshot

YEAR(date) extracts the year number from a date.
MONTH(date) extracts the month number (1-12).
DAY(date) extracts the day number (1-31).
Use these functions to break down dates into parts.
They return integers representing each part.
If input is NULL, output is NULL.

Full Transcript

This visual execution shows how SQL extracts year, month, and day from a date string. Starting with the date '2024-06-15', the YEAR() function extracts 2024, MONTH() extracts 6, and DAY() extracts 15. Each step applies one function and outputs the corresponding part. Variables year_part, month_part, and day_part update as each function runs. The process stops after all parts are extracted, returning a single row with these values. Common confusions include understanding that YEAR() returns only the year, MONTH() never exceeds 12, and all functions return NULL if input is NULL. The quiz tests understanding of these extraction steps and outputs.