YEAR, MONTH, DAY extraction in SQL - Mini Project: Build & Apply

Extract YEAR, MONTH, and DAY from Dates in SQL

📖 Scenario: You work in a company database where customer orders are stored with full date and time stamps. You want to analyze orders by year, month, and day separately to understand seasonal trends.

🎯 Goal: Build a SQL query that extracts the YEAR, MONTH, and DAY from a date column called order_date in the orders table.

📋 What You'll Learn

Create a table called orders with an order_id and order_date column

Insert three specific rows with exact order_date values

Write a query that selects order_id and extracts YEAR(order_date), MONTH(order_date), and DAY(order_date)

Alias the extracted columns as order_year, order_month, and order_day

💡 Why This Matters

🌍 Real World

Extracting parts of dates is common in business reports, sales analysis, and time-based filtering.

💼 Career

Database developers and analysts often write queries to break down dates for trend analysis and reporting.

1

Create the orders table with sample data

Create a table called orders with columns order_id (integer) and order_date (date). Insert these three rows exactly: (1, '2023-01-15'), (2, '2023-12-05'), and (3, '2024-06-20').

SQL

-- Create the orders table and insert data
-- Your code here

Need a hint?

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

2

Set up a SELECT query to retrieve all columns

Write a SELECT query to get all columns from the orders table. Use SELECT * and FROM orders.

SQL

CREATE TABLE orders (
  order_id INT,
  order_date DATE
);

INSERT INTO orders (order_id, order_date) VALUES
  (1, '2023-01-15'),
  (2, '2023-12-05'),
  (3, '2024-06-20');

-- Write SELECT * FROM orders below
-- Your code here

Need a hint?

Use SELECT * FROM orders; to see all data in the table.

3

Extract YEAR, MONTH, and DAY from order_date

Modify the SELECT query to extract the YEAR, MONTH, and DAY from order_date. Select order_id, YEAR(order_date) AS order_year, MONTH(order_date) AS order_month, and DAY(order_date) AS order_day from orders.

SQL

CREATE TABLE orders (
  order_id INT,
  order_date DATE
);

INSERT INTO orders (order_id, order_date) VALUES
  (1, '2023-01-15'),
  (2, '2023-12-05'),
  (3, '2024-06-20');

-- Select order_id and extract YEAR, MONTH, DAY from order_date
-- Your code here

Need a hint?

Use the YEAR(), MONTH(), and DAY() functions to get parts of the date.

4

Complete the query with ordering by order_id

Add an ORDER BY order_id clause at the end of the query to sort results by order_id ascending.

SQL

CREATE TABLE orders (
  order_id INT,
  order_date DATE
);

INSERT INTO orders (order_id, order_date) VALUES
  (1, '2023-01-15'),
  (2, '2023-12-05'),
  (3, '2024-06-20');

SELECT order_id,
       YEAR(order_date) AS order_year,
       MONTH(order_date) AS order_month,
       DAY(order_date) AS order_day
FROM orders
-- Add ORDER BY order_id below
-- Your code here

Need a hint?

Use ORDER BY order_id to sort the results by order ID.