STR_TO_DATE parsing in MySQL - Step-by-Step Execution

Concept Flow - STR_TO_DATE parsing

Input String

↓

Format String

↓

STR_TO_DATE Function

↓

Parse Input Using Format

↓

Extract Date/Time Parts

↓

Construct Date/Time Value

↓

Return DATETIME or NULL if fail

STR_TO_DATE takes a text date and a format, parses the text according to the format, and returns a date/time value or NULL if parsing fails.

Execution Sample

MySQL

SELECT STR_TO_DATE('21/06/2023', '%d/%m/%Y');

Parses the string '21/06/2023' as day/month/year and returns a date value.

Execution Table

Step	Input String	Format String	Parsing Action	Extracted Parts	Result
1	'21/06/2023'	'%d/%m/%Y'	Match '21' to %d (day)	day=21	Continue
2	'21/06/2023'	'%d/%m/%Y'	Match '/' literal	day=21	Continue
3	'21/06/2023'	'%d/%m/%Y'	Match '06' to %m (month)	day=21, month=6	Continue
4	'21/06/2023'	'%d/%m/%Y'	Match '/' literal	day=21, month=6	Continue
5	'21/06/2023'	'%d/%m/%Y'	Match '2023' to %Y (year)	day=21, month=6, year=2023	Continue
6	'21/06/2023'	'%d/%m/%Y'	All parts matched	day=21, month=6, year=2023	Return '2023-06-21 00:00:00'
7	'invalid'	'%d/%m/%Y'	Fail to match day	none	Return NULL

💡 Parsing stops when all format parts matched or a mismatch occurs causing NULL return.

Variable Tracker

Variable	Start	After Step 1	After Step 3	After Step 5	Final
day	NULL	21	21	21	21
month	NULL	NULL	6	6	6
year	NULL	NULL	NULL	2023	2023
result	NULL	NULL	NULL	NULL	'2023-06-21 00:00:00'

Key Moments - 2 Insights

Why does STR_TO_DATE return NULL if the input string doesn't match the format exactly?

Because STR_TO_DATE tries to match each part of the input string to the format tokens step-by-step (see execution_table rows 1-7). If any part fails to match, it cannot build a valid date and returns NULL.

What happens if the input string has extra characters not described in the format?

STR_TO_DATE will fail to match those extra characters since the format expects exact matching (see execution_table row 7). This causes the function to return NULL.

Visual Quiz - 3 Questions

Look at the execution_table at Step 3, what is the value of 'month' after parsing?

A NULL

B 6

C 21

D 2023

💡 Hint

Check the 'Extracted Parts' column at Step 3 in execution_table.

At which step does STR_TO_DATE return the final date value?

A Step 6

B Step 7

C Step 5

D Step 1

💡 Hint

Look for the row where 'Result' column shows the returned date string.

If the input string was '21-06-2023' but format is '%d/%m/%Y', what would STR_TO_DATE return?

A A valid date '2023-06-21'

B Partial date with missing parts

C NULL

D Error message

💡 Hint

Refer to key_moments about exact matching and execution_table row 7.

Concept Snapshot

STR_TO_DATE(string, format) parses a text date using the format tokens.
Format tokens like %d, %m, %Y represent day, month, year.
It matches input parts step-by-step and returns a DATETIME value.
If any mismatch occurs, it returns NULL.
Use exact format matching for successful parsing.

Full Transcript

STR_TO_DATE is a MySQL function that converts a string into a date/time value by parsing it according to a specified format. The function reads the input string and format string step-by-step, matching each part of the input to the corresponding format token like %d for day, %m for month, and %Y for year. If all parts match correctly, it constructs and returns a date/time value. If any part fails to match, the function returns NULL. For example, parsing '21/06/2023' with format '%d/%m/%Y' extracts day=21, month=6, year=2023 and returns '2023-06-21 00:00:00'. If the input string does not exactly match the format, such as using dashes instead of slashes, the function returns NULL. This step-by-step matching process ensures accurate date parsing only when the input matches the expected format exactly.