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.

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