Why DATE_FORMAT and EXTRACT in SQL? - Purpose & Use Cases

The Big Idea

What if you could instantly pull out just the month or year from any date with a simple command?

The Scenario

Imagine you have a big list of dates written in different styles on paper. You want to find all the birthdays in July or just see the year part of each date. Doing this by hand means flipping through pages and guessing the parts of each date.

The Problem

Manually checking each date is slow and mistakes happen easily. You might miss some dates or mix up months and days. It's hard to quickly answer questions like "How many events happened in 2023?" or "Show me all dates in March."

The Solution

Using DATE_FORMAT and EXTRACT in SQL lets you quickly pull out parts of a date like the year, month, or day. You can also change how dates look to match what you need. This makes sorting, filtering, and showing dates easy and error-free.

Before vs After

✗ Before

Look at each date and write down the month and year separately.

✓ After

SELECT EXTRACT(MONTH FROM date_column) AS month, DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM table;

What It Enables

You can instantly find, group, and display dates exactly how you want without confusion or delay.

Real Life Example

A store wants to see sales only from December or find out how many orders came in each year. DATE_FORMAT and EXTRACT help them get these answers fast from their sales records.

Key Takeaways

Manually handling dates is slow and error-prone.

DATE_FORMAT and EXTRACT let you pick and show date parts easily.

This makes date-based questions quick and reliable to answer.

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