STR_TO_DATE parsing in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the MySQL function STR_TO_DATE do?

It converts a string into a date or datetime value based on the specified format.

beginner

Which format specifier in STR_TO_DATE represents a 4-digit year?

%Y represents a 4-digit year, like 2024.

beginner

How would you parse the string '31-12-2023' into a date using STR_TO_DATE?

Use STR_TO_DATE('31-12-2023', '%d-%m-%Y') to convert it to a date.

intermediate

What happens if the string format does not match the STR_TO_DATE format?

The function returns NULL because it cannot parse the string correctly.

beginner

Which format specifier would you use to parse hours in 24-hour format?

%H is used to parse hours in 24-hour format (00 to 23).

What does STR_TO_DATE('2024-06-15', '%Y-%m-%d') return?

A An error

B A string '2024-06-15'

C NULL

D A date value representing June 15, 2024

STR_TO_DATE converts the string to a date using the format '%Y-%m-%d'.

Which format specifier parses the month as a number with leading zero?

A %d

B %M

C %m

D %y

%m parses the month as a two-digit number (01 to 12).

What will STR_TO_DATE('15/06/24', '%d/%m/%Y') return?

A NULL

B June 15, 2024

C June 15, 0024

D An error

The year format '%Y' expects 4 digits, but '24' is only 2 digits, so it returns NULL.

How do you parse a time string like '14:30:00' using STR_TO_DATE?

A STR_TO_DATE('14:30:00', '%h:%m:%s')

B STR_TO_DATE('14:30:00', '%H:%i:%s')

C STR_TO_DATE('14:30:00', '%I:%M:%S')

D STR_TO_DATE('14:30:00', '%H-%i-%s')

%H:%i:%s parses 24-hour time with minutes and seconds.

If STR_TO_DATE returns NULL, what is the most likely cause?

A The string does not match the format

B The string is empty

C The function is deprecated

D The database is offline

STR_TO_DATE returns NULL when the input string does not match the given format.

Explain how STR_TO_DATE works and why the format string is important.

Think about how you read a date written in different styles.

List common format specifiers used in STR_TO_DATE for date and time.

Remember the parts of a date and time you usually see.

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