STR_TO_DATE parsing in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Parsing date with STR_TO_DATE function

What is the output of this query?

SELECT STR_TO_DATE('2024-06-15', '%Y-%m-%d') AS parsed_date;

MySQL

SELECT STR_TO_DATE('2024-06-15', '%Y-%m-%d') AS parsed_date;

A 2024-06-15 00:00:00

B NULL

C 2024-15-06 00:00:00

D Syntax error

💡 Hint

Check the format string matches the input date format exactly.

STR_TO_DATE converts the string '2024-06-15' to a date using the format '%Y-%m-%d'. The result is a DATETIME with time set to 00:00:00.

❓ query_result

intermediate

Parsing date with incorrect format string

What is the output of this query?

SELECT STR_TO_DATE('15/06/2024', '%Y-%m-%d') AS parsed_date;

MySQL

SELECT STR_TO_DATE('15/06/2024', '%Y-%m-%d') AS parsed_date;

A 2024-06-15 00:00:00

B NULL

C 15/06/2024

D Syntax error

💡 Hint

The format string does not match the input string format.

The input string '15/06/2024' does not match the format '%Y-%m-%d', so STR_TO_DATE returns NULL.

📝 Syntax

advanced

Identify the syntax error in STR_TO_DATE usage

Which option contains a syntax error in using STR_TO_DATE?

A SELECT STR_TO_DATE('2024-06-15', '%Y-%m-%d') AS date_val;

B SELECT STR_TO_DATE('06-15-2024', '%m-%d-%Y') AS date_val;

C SELECT STR_TO_DATE('2024/06/15', '%Y/%m/%d') AS date_val;

D SELECT STR_TO_DATE('2024-06-15', '%Y-%m-%d'

💡 Hint

Look for missing parentheses or incomplete function calls.

Option D is missing the closing parenthesis for the STR_TO_DATE function, causing a syntax error.

❓ query_result

advanced

Parsing datetime with STR_TO_DATE including time

What is the output of this query?

SELECT STR_TO_DATE('15-06-2024 14:30:00', '%d-%m-%Y %H:%i:%s') AS parsed_datetime;

MySQL

SELECT STR_TO_DATE('15-06-2024 14:30:00', '%d-%m-%Y %H:%i:%s') AS parsed_datetime;

A NULL

B 15-06-2024 14:30:00

C 2024-06-15 14:30:00

D Syntax error

💡 Hint

Check the format string carefully matches the input including time parts.

The input string matches the format string exactly, so STR_TO_DATE returns the datetime '2024-06-15 14:30:00'.

🧠 Conceptual

expert

Understanding STR_TO_DATE behavior with invalid date parts

What will STR_TO_DATE return when parsing this string?

SELECT STR_TO_DATE('2024-13-40', '%Y-%m-%d') AS result;

MySQL

SELECT STR_TO_DATE('2024-13-40', '%Y-%m-%d') AS result;

A NULL

B 2024-13-40 00:00:00

C 2024-01-09 00:00:00

D Syntax error

💡 Hint

STR_TO_DATE returns NULL for invalid date components like month >12 or invalid days.

The input '2024-13-40' has invalid month 13 and day 40, so STR_TO_DATE returns NULL.