DATE_FORMAT and EXTRACT in SQL - Step-by-Step Execution

Concept Flow - DATE_FORMAT and EXTRACT

Start with a date/time value

↓

DATE_FORMAT

↓

Format date to

↓

string output

↓

Use result in query

You start with a date/time value, then pick DATE_FORMAT to get a formatted string or EXTRACT to get a specific part as a number.

Execution Sample

SQL

SELECT DATE_FORMAT('2024-06-15 14:30:00', '%Y-%m-%d') AS formatted_date,
       EXTRACT(HOUR FROM '2024-06-15 14:30:00') AS hour_part;

This query formats the date to 'YYYY-MM-DD' string and extracts the hour part as a number.

Execution Table

Step	Function	Input Date	Format/Part	Output	Explanation
1	DATE_FORMAT	'2024-06-15 14:30:00'	'%Y-%m-%d'	'2024-06-15'	Formats date to year-month-day string
2	EXTRACT	'2024-06-15 14:30:00'	HOUR	14	Extracts hour part as number
3	END				Query returns both results

💡 All functions applied to input date, results ready for output

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Input Date	'2024-06-15 14:30:00'	'2024-06-15 14:30:00'	'2024-06-15 14:30:00'	'2024-06-15 14:30:00'
Formatted Date	NULL	'2024-06-15'	'2024-06-15'	'2024-06-15'
Extracted Hour	NULL	NULL	14	14

Key Moments - 2 Insights

Why does DATE_FORMAT return a string but EXTRACT returns a number?

DATE_FORMAT converts the date into a formatted text string based on the pattern (see Step 1 in execution_table), while EXTRACT pulls out a numeric part like hour or day (see Step 2). They serve different purposes.

Can EXTRACT get parts like year, month, day, hour, minute?

Yes, EXTRACT can get many parts such as YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. This is shown in Step 2 where HOUR is extracted.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output of DATE_FORMAT at Step 1?

A '2024-06-15'

B '14:30:00'

C 14

D '2024/06/15'

💡 Hint

Check the 'Output' column in Step 1 of execution_table

At which step does EXTRACT return the hour part of the date?

A Step 1

B Step 2

C Step 3

D EXTRACT does not return hour

💡 Hint

Look at the 'Function' and 'Output' columns in execution_table

If you change the format in DATE_FORMAT to '%H:%i', what would the output be at Step 1?

A '14:30'

B '2024-06-15'

C '15:14'

D '2024/06/15'

💡 Hint

DATE_FORMAT outputs string based on format pattern, see Step 1 output for '%Y-%m-%d'

Concept Snapshot

DATE_FORMAT(date, format) returns a formatted string of the date.
EXTRACT(part FROM date) returns a numeric part (year, month, day, hour, etc).
Use DATE_FORMAT to display dates nicely.
Use EXTRACT to get specific date parts for calculations.
Both work on date/time values.
Formats and parts must be valid for correct output.

Full Transcript

This visual execution shows how SQL functions DATE_FORMAT and EXTRACT work on a date/time value. First, the input date '2024-06-15 14:30:00' is given. DATE_FORMAT formats this date into a string '2024-06-15' using the pattern '%Y-%m-%d'. EXTRACT pulls out the hour part as the number 14. The execution table traces these steps clearly, showing inputs, formats, and outputs. The variable tracker shows how the input date stays the same, while formatted date and extracted hour get their values. Key moments clarify why DATE_FORMAT returns a string and EXTRACT returns a number, and what parts EXTRACT can get. The quiz tests understanding by asking about outputs at specific steps and effects of changing the format. The snapshot summarizes usage and differences. This helps beginners see exactly how these functions transform date values step-by-step.

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