STR_TO_DATE parsing in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to convert the string '2024-06-15' to a date using STR_TO_DATE.

MySQL

SELECT STR_TO_DATE('2024-06-15', [1]);

Drag options to blanks, or click blank then click option'

A'%Y-%m-%d'

B'%d/%m/%Y'

C'%m-%d-%Y'

D'%Y/%d/%m'

Attempts:

3 left

💡 Hint

Common Mistakes

Using slashes '/' instead of dashes '-' in the format string.

Mixing up the order of day and month.

The format '%Y-%m-%d' matches the string '2024-06-15' where %Y is year, %m is month, and %d is day.

2fill in blank

medium

Complete the code to parse '15/06/2024' into a date using STR_TO_DATE.

MySQL

SELECT STR_TO_DATE('15/06/2024', [1]);

Drag options to blanks, or click blank then click option'

A'%Y-%m-%d'

B'%d/%m/%Y'

C'%m/%d/%Y'

D'%Y/%d/%m'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '%m/%d/%Y' which expects month first.

Using dashes '-' instead of slashes '/'.

The string uses day/month/year with slashes, so the format is '%d/%m/%Y'.

3fill in blank

hard

Fix the error in the code to correctly parse '06-15-2024' as a date.

MySQL

SELECT STR_TO_DATE('06-15-2024', [1]);

Drag options to blanks, or click blank then click option'

A'%Y-%d-%m'

B'%d-%m-%Y'

C'%Y-%m-%d'

D'%m-%d-%Y'

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping day and month in the format string.

Using year first format incorrectly.

The string '06-15-2024' has month first, then day, then year, so the format is '%m-%d-%Y'.

4fill in blank

hard

Complete the code to parse '15-JUN-2024' into a date using STR_TO_DATE.

MySQL

SELECT STR_TO_DATE('15-JUN-2024', [1]);

Drag options to blanks, or click blank then click option'

A'%Y-%b-%d'

B'%d-%B-%Y'

C'%d-%b-%Y'

D'%m-%d-%Y'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '%B' which expects full month name.

Mixing up the order of day and month.

The string uses day, abbreviated month name, and year with dashes. '%b' is for abbreviated month name.

5fill in blank

hard

Complete the code to parse '2024 June 15' into a date using STR_TO_DATE.

MySQL

SELECT STR_TO_DATE('2024 June 15', [1]);

Drag options to blanks, or click blank then click option'

A'%Y %B %d'

B'%Y %b %d'

C'%d'

D'%m'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '%b' which expects abbreviated month name.

Mixing up day and month positions.

The string has year, full month name, and day separated by spaces. '%B' is full month name, '%d' is day.

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