DATE_FORMAT function in MySQL - Step-by-Step Execution

Concept Flow - DATE_FORMAT function

Input: Date/Datetime

↓

Apply DATE_FORMAT with format string

↓

Parse format specifiers

↓

Replace specifiers with date parts

↓

Return formatted string

The DATE_FORMAT function takes a date or datetime and a format string, then replaces format codes with parts of the date to produce a formatted text.

Execution Sample

MySQL

SELECT DATE_FORMAT('2024-06-15 14:30:45', '%Y-%m-%d %H:%i:%s');

Formats the given datetime into 'Year-Month-Day Hour:Minute:Second' text.

Execution Table

Step	Format Specifier	Date Part Extracted	Replacement Text	Intermediate Result
1	%Y	Year (2024)	2024	2024
2	%m	Month (06)	06	2024-06
3	%d	Day (15)	15	2024-06-15
4	%H	Hour (14)	14	2024-06-15 14
5	%i	Minute (30)	30	2024-06-15 14:30
6	%s	Second (45)	45	2024-06-15 14:30:45
7	No more specifiers	N/A	N/A	2024-06-15 14:30:45

💡 All format specifiers replaced, final formatted string returned.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
formatted_string	''	'2024'	'2024-06'	'2024-06-15'	'2024-06-15 14'	'2024-06-15 14:30'	'2024-06-15 14:30:45'	'2024-06-15 14:30:45'

Key Moments - 3 Insights

Why does the output show '06' for the month instead of '6'?

Because the format specifier '%m' always returns the month as two digits with a leading zero if needed, as shown in execution_table step 2.

What happens if the format string has text without % specifiers?

That text is included as-is in the output. Only parts starting with % are replaced, so normal characters remain unchanged.

Why is the final output a string and not a date type?

DATE_FORMAT returns a formatted text string, not a date type, because it converts date parts into readable text as shown in the final step of execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the intermediate result after replacing '%d'?

A 2024-06-15 14

B 2024-06

C 2024-06-15

D 2024

💡 Hint

Check the intermediate result column at step 3 where '%d' is replaced.

At which step does the formatted string include the hour part?

A Step 4

B Step 3

C Step 5

D Step 6

💡 Hint

Look at the format specifier column and see when '%H' is replaced.

If the format string was '%Y/%m/%d', what would be the output after step 2?

A 2024-06

B 2024/06

C 2024/06/15

D 2024-06-15

💡 Hint

Refer to how separators in the format string appear in the intermediate result in execution_table.

Concept Snapshot

DATE_FORMAT(date, format) returns a string formatted from date.
Use %Y for 4-digit year, %m for 2-digit month, %d for day.
Other codes: %H hour, %i minute, %s second.
Non-% text is copied as-is.
Output is always text, not date type.

Full Transcript

The DATE_FORMAT function in MySQL takes a date or datetime value and a format string. It reads the format string from left to right, replacing each format specifier like %Y, %m, %d with the corresponding part of the date. For example, %Y becomes the 4-digit year, %m the 2-digit month with leading zero, and so on. Text in the format string that is not a specifier is included unchanged. The function returns the final formatted string. This process is stepwise: each specifier is replaced in order, building the output string. The output is always a text string, not a date type. This helps display dates in readable or custom formats.