EXTRACT and YEAR/MONTH/DAY in MySQL - Mini Project: Build & Apply

Extract Year, Month, and Day from Dates in MySQL

📖 Scenario: You are managing a small library database. You want to organize and analyze the dates when books were borrowed. To do this, you need to extract the year, month, and day parts from the borrow dates stored in your database.

🎯 Goal: Build a MySQL query that extracts the year, month, and day from a date column called borrow_date in a table named borrow_records. This will help you see the exact year, month, and day when each book was borrowed.

📋 What You'll Learn

Create a table called borrow_records with columns id (integer) and borrow_date (date).

Insert three specific rows with exact dates: '2023-04-15', '2022-12-01', and '2024-01-20'.

Write a SELECT query that extracts the year, month, and day from borrow_date using the EXTRACT function.

Alias the extracted parts as year_borrowed, month_borrowed, and day_borrowed.

💡 Why This Matters

🌍 Real World

Extracting parts of dates is common in reporting and analytics to group or filter data by year, month, or day.

💼 Career

Database administrators and analysts often use date extraction to prepare data for business insights and decision-making.

1

Create the borrow_records table and insert data

Create a table called borrow_records with columns id as an integer primary key and borrow_date as a date. Insert three rows with id values 1, 2, 3 and borrow_date values '2023-04-15', '2022-12-01', and '2024-01-20' respectively.

MySQL

-- Create the table and insert the three rows below
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows with exact dates.

2

Set up the SELECT query base

Write a SELECT query that selects all columns from borrow_records. This will be the base for adding the extraction of year, month, and day.

MySQL

CREATE TABLE borrow_records (
    id INT PRIMARY KEY,
    borrow_date DATE
);

INSERT INTO borrow_records (id, borrow_date) VALUES
(1, '2023-04-15'),
(2, '2022-12-01'),
(3, '2024-01-20');

-- Write a SELECT query to get all columns from borrow_records
-- Your code here

Hint

Use SELECT * FROM borrow_records; to select all columns.

3

Extract year, month, and day from borrow_date

Modify the SELECT query to extract the year, month, and day from the borrow_date column using the EXTRACT function. Alias the extracted year as year_borrowed, the month as month_borrowed, and the day as day_borrowed.

MySQL

CREATE TABLE borrow_records (
    id INT PRIMARY KEY,
    borrow_date DATE
);

INSERT INTO borrow_records (id, borrow_date) VALUES
(1, '2023-04-15'),
(2, '2022-12-01'),
(3, '2024-01-20');

-- Write a SELECT query that extracts year, month, and day from borrow_date
-- Alias them as year_borrowed, month_borrowed, and day_borrowed
-- Your code here

Hint

Use EXTRACT(YEAR FROM borrow_date) to get the year part. Do the same for month and day. Use AS to rename the columns.

4

Complete the query with all required columns

Complete the SELECT query by including the id column and the extracted year, month, and day columns from borrow_date. This will give a clear view of each record's ID and the exact year, month, and day it was borrowed.

MySQL

CREATE TABLE borrow_records (
    id INT PRIMARY KEY,
    borrow_date DATE
);

INSERT INTO borrow_records (id, borrow_date) VALUES
(1, '2023-04-15'),
(2, '2022-12-01'),
(3, '2024-01-20');

-- Finalize the SELECT query with id and extracted date parts
-- Your code here

Hint

Make sure the SELECT statement includes id and all three extracted parts with their aliases.

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