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.

Practice

1. What does the MySQL function STR_TO_DATE('2024-06-15', '%Y-%m-%d') do?

easy

A. Throws an error because the format string is incorrect.

B. Converts the string into a number representing the date.

C. Converts the string '2024-06-15' into a date value using the format year-month-day.

D. Returns the string unchanged because STR_TO_DATE only works with time values.

Solution

1. Step 1: Understand STR_TO_DATE purpose

   STR_TO_DATE converts a text string into a date or datetime value based on the given format.

2. Step 2: Match the string and format

   The string '2024-06-15' matches the format '%Y-%m-%d' which means year-month-day.

3. Final Answer:

   Converts the string '2024-06-15' into a date value using the format year-month-day. -> Option C

4. Quick Check:

   STR_TO_DATE('2024-06-15', '%Y-%m-%d') = date value [OK]

Hint: Match format exactly to string parts for correct conversion [OK]

Common Mistakes:

• Confusing format specifiers like %m and %d
• Expecting STR_TO_DATE to return a string
• Using wrong format causing NULL result

2. Which of the following is the correct syntax to convert the string '15/06/2024' to a date using STR_TO_DATE in MySQL?

easy

A. STR_TO_DATE('15/06/2024', '%Y/%m/%d')

B. STR_TO_DATE('15/06/2024', '%d-%m-%Y')

C. STR_TO_DATE('15/06/2024', '%m/%d/%Y')

D. STR_TO_DATE('15/06/2024', '%d/%m/%Y')

Solution

1. Step 1: Identify the date format in the string

   The string uses slashes '/' and the order is day/month/year.

2. Step 2: Choose matching format specifiers

   Use '%d/%m/%Y' to match day/month/year with slashes.

3. Final Answer:

   STR_TO_DATE('15/06/2024', '%d/%m/%Y') -> Option D

4. Quick Check:

   Format matches string separators and order [OK]

Hint: Match separators and order exactly in format string [OK]

Common Mistakes:

• Using wrong separators like '-' instead of '/'
• Swapping month and day positions
• Using uppercase %M instead of %m

3. What is the result of this query?
SELECT STR_TO_DATE('07-24-2024 14:30:00', '%m-%d-%Y %H:%i:%s');

medium

A. 2024-07-24 14:30:00

B. 24-07-2024 14:30:00

C. NULL

D. 2024-24-07 14:30:00

Solution

1. Step 1: Analyze the input string and format

   The string is '07-24-2024 14:30:00' which is month-day-year hour:minute:second.

2. Step 2: Confirm format matches string

   The format '%m-%d-%Y %H:%i:%s' matches month-day-year and time correctly.

3. Final Answer:

   2024-07-24 14:30:00 -> Option A

4. Quick Check:

   STR_TO_DATE parses string to datetime correctly [OK]

Hint: Check order of date parts and time specifiers carefully [OK]

Common Mistakes:

• Confusing day and month order
• Using %M instead of %m for month number
• Expecting string output instead of date/datetime

4. Identify the error in this query:
SELECT STR_TO_DATE('2024/06/15', '%Y-%m-%d');

medium

A. The format string uses dashes but the date string uses slashes.

B. The year format specifier %Y is incorrect.

C. STR_TO_DATE cannot parse dates with slashes.

D. The function requires a time part to work.

Solution

1. Step 1: Compare date string and format separators

   The string uses '/' but the format uses '-' as separators.

2. Step 2: Understand impact of mismatched separators

   STR_TO_DATE expects exact match; mismatch causes NULL or error.

3. Final Answer:

   The format string uses dashes but the date string uses slashes. -> Option A

4. Quick Check:

   Separators must match exactly [OK]

Hint: Check separators in string and format carefully [OK]

Common Mistakes:

• Ignoring separator mismatch
• Thinking %Y is wrong for year
• Assuming time part is mandatory

5. You have a table with a column event_date storing dates as strings like '15-June-2024'. Which STR_TO_DATE format correctly converts this to a date?

hard

A. STR_TO_DATE(event_date, '%d-%m-%Y')

B. STR_TO_DATE(event_date, '%d-%M-%Y')

C. STR_TO_DATE(event_date, '%Y-%m-%d')

D. STR_TO_DATE(event_date, '%M-%d-%Y')

Solution

1. Step 1: Analyze the string format

   The string is '15-June-2024' with day, full month name, and year separated by dashes.

2. Step 2: Match format specifiers

   %d = day number, %M = full month name, %Y = 4-digit year, with dashes as separators.

3. Final Answer:

   STR_TO_DATE(event_date, '%d-%M-%Y') -> Option B

4. Quick Check:

   Use %M for full month name, not %m [OK]

Hint: Use %M for full month names, %m for numbers [OK]

Common Mistakes:

• Using %m for month name instead of number
• Swapping day and month positions
• Ignoring separator characters