EXTRACT and YEAR/MONTH/DAY in MySQL - Step-by-Step Execution

Concept Flow - EXTRACT and YEAR/MONTH/DAY

Start with a date/time value

↓

Choose part to extract: YEAR, MONTH, or DAY

↓

Use EXTRACT function

↓

Return the chosen part as a number

↓

Use result in query or display

The EXTRACT function takes a date/time and returns the year, month, or day part as a number.

Execution Sample

MySQL

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

This query extracts the year, month, and day parts from the date '2024-06-15'.

Execution Table

Step	Function Call	Input Date	Extracted Part	Result
1	EXTRACT(YEAR FROM '2024-06-15')	2024-06-15	YEAR	2024
2	EXTRACT(MONTH FROM '2024-06-15')	2024-06-15	MONTH	6
3	EXTRACT(DAY FROM '2024-06-15')	2024-06-15	DAY	15
4	Query completes	-	-	Returns one row with year=2024, month=6, day=15

💡 All parts extracted successfully; query returns one row with three columns.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
year_part	NULL	2024	2024	2024	2024
month_part	NULL	NULL	6	6	6
day_part	NULL	NULL	NULL	15	15

Key Moments - 2 Insights

Why does EXTRACT return a number and not a date?

EXTRACT returns only the specified part (year, month, or day) as a number, not the full date. See execution_table rows 1-3 where each call returns a number.

Can EXTRACT be used on date strings directly?

Yes, as long as the string is in a valid date format like 'YYYY-MM-DD'. The example uses '2024-06-15' directly (execution_table input).

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of EXTRACT(MONTH FROM '2024-06-15')?

A 15

B 2024

C 6

D NULL

💡 Hint

Check execution_table row 2 under Result column.

At which step does the day part get extracted?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at execution_table rows and see which step extracts DAY.

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

A 1

B 2023

C 12

D NULL

💡 Hint

EXTRACT(YEAR FROM ...) returns the year part as shown in variable_tracker and execution_table.

Concept Snapshot

EXTRACT(part FROM date) extracts a part of a date.
Common parts: YEAR, MONTH, DAY.
Returns a number representing that part.
Input date must be valid format.
Useful to get date components for filtering or display.

Full Transcript

This lesson shows how to use the EXTRACT function in MySQL to get the year, month, or day from a date. The function takes two inputs: the part you want (YEAR, MONTH, or DAY) and the date value. It returns the number for that part. For example, EXTRACT(YEAR FROM '2024-06-15') returns 2024. We traced each step where the year, month, and day are extracted separately. The variables year_part, month_part, and day_part hold these values after each step. This helps when you want to filter or display parts of dates in your database queries.

Practice

1. What does the MySQL function EXTRACT(YEAR FROM '2023-07-15') return?

easy

A. 07

B. '2023-07-15'

C. 15

D. 2023

Solution

1. Step 1: Understand EXTRACT function usage

   The EXTRACT function retrieves a specific part of a date, here the YEAR part.

2. Step 2: Apply EXTRACT to the given date

   Extracting YEAR from '2023-07-15' returns 2023 as a number.

3. Final Answer:

   2023 -> Option D

4. Quick Check:

   EXTRACT(YEAR FROM date) = year number [OK]

Hint: EXTRACT(YEAR FROM date) gives the year number [OK]

Common Mistakes:

• Confusing YEAR with MONTH or DAY
• Expecting a string instead of a number
• Using EXTRACT without FROM keyword

2. Which of the following is the correct syntax to extract the month from a date column named order_date in MySQL?

easy

A. EXTRACT(MONTH FROM order_date)

B. EXTRACT(order_date FROM MONTH)

C. EXTRACT(MONTH order_date)

D. EXTRACT(MONTH, order_date)

Solution

1. Step 1: Recall EXTRACT syntax

   The correct syntax is EXTRACT(part FROM date), where part is YEAR, MONTH, or DAY.

2. Step 2: Match syntax to options

   EXTRACT(MONTH FROM order_date) uses EXTRACT(MONTH FROM order_date), which is correct syntax.

3. Final Answer:

   EXTRACT(MONTH FROM order_date) -> Option A

4. Quick Check:

   EXTRACT(part FROM date) syntax is correct [OK]

Hint: Use EXTRACT(part FROM date) syntax exactly [OK]

Common Mistakes:

• Omitting FROM keyword
• Swapping arguments order
• Using commas instead of FROM

3. Given the table events with a column event_date storing dates, what will this query return?

SELECT EXTRACT(DAY FROM event_date) AS day FROM events WHERE EXTRACT(MONTH FROM event_date) = 12;

medium

A. Syntax error due to wrong EXTRACT usage

B. All days of events that happened in December

C. All years of events that happened in December

D. All months of events that happened in December

Solution

1. Step 1: Understand the WHERE clause

   The WHERE clause filters rows where the month part of event_date is 12 (December).

2. Step 2: Understand the SELECT clause

   The SELECT clause extracts the day part from event_date for those filtered rows.

3. Final Answer:

   All days of events that happened in December -> Option B

4. Quick Check:

   EXTRACT(MONTH) filters December, EXTRACT(DAY) selects day [OK]

Hint: Filter by EXTRACT(MONTH), select EXTRACT(DAY) for days [OK]

Common Mistakes:

• Confusing day and month extraction
• Thinking EXTRACT returns strings
• Assuming syntax error without checking carefully

4. Identify the error in this query:

SELECT EXTRACT(YEAR order_date) FROM orders;

medium

A. EXTRACT cannot be used with YEAR

B. order_date should be in quotes

C. Missing FROM keyword after YEAR

D. No error, query is correct

Solution

1. Step 1: Check EXTRACT syntax

   EXTRACT requires the format EXTRACT(part FROM date), so FROM keyword is mandatory.

2. Step 2: Identify missing FROM keyword

   The query misses FROM between YEAR and order_date, causing syntax error.

3. Final Answer:

   Missing FROM keyword after YEAR -> Option C

4. Quick Check:

   EXTRACT(part FROM date) needs FROM [OK]

Hint: Always include FROM between part and date in EXTRACT [OK]

Common Mistakes:

• Omitting FROM keyword
• Putting column name in quotes
• Thinking EXTRACT can't use YEAR

5. You want to count how many orders were placed each year and month from the orders table with a created_at datetime column. Which query correctly groups by year and month using EXTRACT?

hard

A. SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

B. SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

C. SELECT EXTRACT(YEAR, created_at) AS year, EXTRACT(MONTH, created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

D. SELECT EXTRACT(YEAR FROM created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

Solution

1. Step 1: Understand grouping by year and month

   We need to extract year and month parts from created_at to group orders by these parts.

2. Step 2: Check EXTRACT syntax correctness

   SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; uses correct EXTRACT syntax for both year and month and groups by aliases year and month.

3. Step 3: Verify other options

   SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; uses YEAR() and MONTH() functions which also work but question asks for EXTRACT usage. SELECT EXTRACT(YEAR, created_at) AS year, EXTRACT(MONTH, created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; has wrong EXTRACT syntax. SELECT EXTRACT(YEAR FROM created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; mixes EXTRACT and MONTH() inconsistently.

4. Final Answer:

   Option A query with correct EXTRACT syntax and grouping -> Option A

5. Quick Check:

   Use EXTRACT(part FROM date) consistently for grouping [OK]

Hint: Use EXTRACT(part FROM date) for both year and month [OK]

Common Mistakes:

• Using wrong EXTRACT syntax with commas
• Mixing EXTRACT and other date functions inconsistently
• Not grouping by extracted aliases