DATE_FORMAT and EXTRACT in SQL - Mini Project: Build & Apply

Using DATE_FORMAT and EXTRACT to Analyze Sales Dates

📖 Scenario: You work for a retail company that tracks sales data. Each sale has a date stored in the database. You want to learn how to format these dates and extract parts like the year and month to better understand sales trends.

🎯 Goal: Build SQL queries that use DATE_FORMAT to display dates in a friendly way and EXTRACT to get the year and month from sales dates.

📋 What You'll Learn

Create a table called sales with columns sale_id (integer) and sale_date (date).

Insert specific sales dates into the sales table.

Write a query using DATE_FORMAT to show sales dates as 'Month Day, Year'.

Write a query using EXTRACT to get the year and month from each sale date.

💡 Why This Matters

🌍 Real World

Formatting and extracting parts of dates is common in sales reports, analytics, and scheduling systems.

💼 Career

Database professionals often write queries to transform and analyze date data for business insights.

1

Create the sales table and insert data

Create a table called sales with columns sale_id as integer and sale_date as date. Then insert these rows exactly: (1, '2023-01-15'), (2, '2023-03-22'), (3, '2024-06-05').

SQL

-- Create the sales table and insert the given rows
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Add a query to format the sale dates

Write a SQL query selecting sale_id and the sale_date formatted as 'Month Day, Year' using DATE_FORMAT. Name the formatted column formatted_date.

SQL

CREATE TABLE sales (
  sale_id INT,
  sale_date DATE
);

INSERT INTO sales (sale_id, sale_date) VALUES
  (1, '2023-01-15'),
  (2, '2023-03-22'),
  (3, '2024-06-05');

-- Write a SELECT query using DATE_FORMAT to show sale_id and formatted_date
-- Your code here

Need a hint?

Use DATE_FORMAT(date, format) with format '%M %e, %Y' to get 'Month Day, Year'.

3

Extract the year and month from sale dates

Write a SQL query selecting sale_id, the year extracted from sale_date as sale_year, and the month extracted as sale_month using EXTRACT.

SQL

CREATE TABLE sales (
  sale_id INT,
  sale_date DATE
);

INSERT INTO sales (sale_id, sale_date) VALUES
  (1, '2023-01-15'),
  (2, '2023-03-22'),
  (3, '2024-06-05');

SELECT sale_id, DATE_FORMAT(sale_date, '%M %e, %Y') AS formatted_date FROM sales;

-- Write a SELECT query using EXTRACT to get sale_year and sale_month
-- Your code here

Need a hint?

Use EXTRACT(YEAR FROM date) and EXTRACT(MONTH FROM date) to get year and month.

4

Combine formatted date and extracted parts in one query

Write a SQL query selecting sale_id, the formatted date as formatted_date using DATE_FORMAT, and the extracted year and month as sale_year and sale_month using EXTRACT from the sales table.

SQL

CREATE TABLE sales (
  sale_id INT,
  sale_date DATE
);

INSERT INTO sales (sale_id, sale_date) VALUES
  (1, '2023-01-15'),
  (2, '2023-03-22'),
  (3, '2024-06-05');

SELECT sale_id, DATE_FORMAT(sale_date, '%M %e, %Y') AS formatted_date FROM sales;

SELECT sale_id, EXTRACT(YEAR FROM sale_date) AS sale_year, EXTRACT(MONTH FROM sale_date) AS sale_month FROM sales;

-- Write a single SELECT query combining formatted_date, sale_year, and sale_month
-- Your code here

Need a hint?

Combine DATE_FORMAT and EXTRACT in the same SELECT statement.