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

Need a 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

Need a 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

Need a 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

Need a hint?

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