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

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

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

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

Hint

Combine DATE_FORMAT and EXTRACT in the same SELECT statement.

Practice

1. What does the SQL function EXTRACT(MONTH FROM '2024-06-15') return?

easy

A. '2024-06-15'

B. 15

C. 2024

D. 6

Solution

1. Step 1: Understand EXTRACT function

   The EXTRACT function pulls out a specific part of a date, here the MONTH part.

2. Step 2: Apply EXTRACT to the date

   From the date '2024-06-15', the month is 6 (June).

3. Final Answer:

   6 -> Option D

4. Quick Check:

   EXTRACT(MONTH FROM date) = 6 [OK]

Hint: EXTRACT pulls one date part, like month or year [OK]

Common Mistakes:

• Confusing day with month
• Expecting full date instead of part
• Using wrong syntax for EXTRACT

2. Which of the following is the correct syntax to get the year from a date using EXTRACT in SQL?

easy

A. EXTRACT(YEAR FROM '2024-06-15')

B. EXTRACT YEAR FROM '2024-06-15'

C. EXTRACT('YEAR', '2024-06-15')

D. EXTRACT('2024-06-15' FROM YEAR)

Solution

1. Step 1: Recall EXTRACT syntax

   The correct syntax is EXTRACT(part FROM date), with parentheses and FROM keyword.

2. Step 2: Check each option

   EXTRACT(YEAR FROM '2024-06-15') matches the correct syntax: EXTRACT(YEAR FROM '2024-06-15'). Others miss parentheses or have wrong order.

3. Final Answer:

   EXTRACT(YEAR FROM '2024-06-15') -> Option A

4. Quick Check:

   Correct EXTRACT syntax uses parentheses and FROM [OK]

Hint: EXTRACT needs parentheses and FROM keyword [OK]

Common Mistakes:

• Omitting parentheses
• Swapping arguments order
• Using quotes around part name

3. What is the output of this SQL query?

SELECT DATE_FORMAT('2024-06-15', '%Y-%m-%d'), EXTRACT(DAY FROM '2024-06-15');

medium

A. 2024-06-15, 6

B. 2024-06-15, 15

C. 2024/06/15, 15

D. 06-15-2024, 15

Solution

1. Step 1: Understand DATE_FORMAT with '%Y-%m-%d'

   This format outputs year-month-day as '2024-06-15'.

2. Step 2: Understand EXTRACT(DAY FROM date)

   Extracting DAY from '2024-06-15' gives 15.

3. Final Answer:

   2024-06-15, 15 -> Option B

4. Quick Check:

   DATE_FORMAT + EXTRACT day = '2024-06-15', 15 [OK]

Hint: DATE_FORMAT '%Y-%m-%d' matches original date format [OK]

Common Mistakes:

• Mixing month and day order
• Wrong format string in DATE_FORMAT
• Extracting wrong date part

4. Identify the error in this SQL query:

SELECT DATE_FORMAT('2024-06-15', '%Y-%d-%m');

medium

A. The date string must be in MM-DD-YYYY format

B. DATE_FORMAT requires three arguments

C. The format string swaps day and month incorrectly

D. EXTRACT should be used instead of DATE_FORMAT

Solution

1. Step 1: Analyze the format string '%Y-%d-%m'

   This format prints year, then day, then month, which is unusual and likely a mistake.

2. Step 2: Understand common date format order

   Usually, month comes before day as '%Y-%m-%d'. Swapping day and month causes confusing output.

3. Final Answer:

   The format string swaps day and month incorrectly -> Option C

4. Quick Check:

   Correct order is year-month-day, not year-day-month [OK]

Hint: Check format order: month before day usually [OK]

Common Mistakes:

• Mixing day and month in format string
• Wrong number of arguments to DATE_FORMAT
• Confusing DATE_FORMAT with EXTRACT

5. You want to list all orders from a table orders showing only the year and month of the order date in format YYYY-MM. Which SQL query correctly does this?

hard

A. SELECT DATE_FORMAT(order_date, '%Y-%m') AS year_month FROM orders;

B. SELECT EXTRACT(YEAR_MONTH FROM order_date) AS year_month FROM orders;

C. SELECT DATE_FORMAT(order_date, '%Y-%d') AS year_month FROM orders;

D. SELECT EXTRACT(MONTH FROM order_date) AS year_month FROM orders;

Solution

1. Step 1: Understand DATE_FORMAT for year and month

   Using '%Y-%m' in DATE_FORMAT returns year and month as 'YYYY-MM'.

2. Step 2: Check other options

   SELECT EXTRACT(YEAR_MONTH FROM order_date) AS year_month FROM orders; is invalid syntax; EXTRACT does not support YEAR_MONTH. SELECT DATE_FORMAT(order_date, '%Y-%d') AS year_month FROM orders; uses day instead of month. SELECT EXTRACT(MONTH FROM order_date) AS year_month FROM orders; returns only month number, missing year.

3. Final Answer:

   SELECT DATE_FORMAT(order_date, '%Y-%m') AS year_month FROM orders; -> Option A

4. Quick Check:

   DATE_FORMAT with '%Y-%m' gives year-month [OK]

Hint: Use DATE_FORMAT with '%Y-%m' for year and month [OK]

Common Mistakes:

• Using EXTRACT with unsupported parts
• Mixing day and month in format
• Selecting only month without year