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.