EXTRACT and YEAR/MONTH/DAY in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Extract Year from Date

Given a table orders with a column order_date of type DATE, what is the output of this query?

SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders WHERE order_id = 101;

MySQL

CREATE TABLE orders (order_id INT, order_date DATE);
INSERT INTO orders VALUES (101, '2023-07-15');

A 07

B 2023

C 15

D 2023-07-15

💡 Hint

EXTRACT(YEAR FROM date) returns the year part as a number.

The EXTRACT function with YEAR returns only the year part of the date, which is 2023 for '2023-07-15'.

❓ query_result

intermediate

Extract Month from Date

What will this query return for the order_date '2022-12-05' in the orders table?

SELECT EXTRACT(MONTH FROM order_date) AS order_month FROM orders WHERE order_id = 202;

MySQL

CREATE TABLE orders (order_id INT, order_date DATE);
INSERT INTO orders VALUES (202, '2022-12-05');

A 12

B 2022

C 2022-12-05

D 5

💡 Hint

EXTRACT(MONTH FROM date) returns the month number.

The EXTRACT function with MONTH returns the month part of the date, which is 12 for December.

📝 Syntax

advanced

Identify the Syntax Error in EXTRACT Usage

Which option contains a syntax error when trying to extract the day from a date column birth_date in table users?

MySQL

SELECT EXTRACT(DAY FROM birth_date) AS birth_day FROM users;

A SELECT EXTRACT(DAY OF birth_date) AS birth_day FROM users;

B SELECT EXTRACT(DAY FROM birth_date) AS birth_day FROM users;

C SELECT EXTRACT(DAY, birth_date) AS birth_day FROM users;

D SELECT EXTRACT(DAY FROM birth_date) birth_day FROM users;

💡 Hint

EXTRACT syntax requires the keyword FROM between the part and the date.

Option C uses a comma instead of the required 'FROM' keyword, causing a syntax error.

❓ optimization

advanced

Optimizing Date Filtering Using EXTRACT

You want to find all orders placed in March 2023 from the orders table with a order_date column. Which query is more efficient and why?

A SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023 AND EXTRACT(MONTH FROM order_date) = 3;

B SELECT * FROM orders WHERE MONTH(order_date) = 3 AND YEAR(order_date) = 2023;

C SELECT * FROM orders WHERE DATE_FORMAT(order_date, '%Y-%m') = '2023-03';

D SELECT * FROM orders WHERE order_date >= '2023-03-01' AND order_date < '2023-04-01';

💡 Hint

Using range conditions on the date column can use indexes better than functions on the column.

Option D uses a date range which allows MySQL to use indexes on order_date efficiently, unlike functions like EXTRACT or DATE_FORMAT which prevent index use.

🧠 Conceptual

expert

Understanding EXTRACT Behavior with NULL Dates

What is the result of this query when order_date is NULL in the orders table?

SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders WHERE order_id = 303;

MySQL

CREATE TABLE orders (order_id INT, order_date DATE);
INSERT INTO orders VALUES (303, NULL);

A NULL

B 0

C Error: Invalid date

D Empty result set

💡 Hint

EXTRACT returns NULL when the date value is NULL.

When the date is NULL, EXTRACT returns NULL instead of a number or error.

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