STR_TO_DATE parsing in MySQL - Mini Project: Build & Apply

Parsing Dates with STR_TO_DATE in MySQL

📖 Scenario: You work in a company that stores customer sign-up dates as text strings in a MySQL database. The dates are in different formats, and you need to convert them into proper date values to analyze sign-up trends.

🎯 Goal: Learn how to use the STR_TO_DATE function in MySQL to convert text strings into date values by specifying the correct format.

📋 What You'll Learn

Create a table called customers with columns id (integer) and signup_date_text (varchar).

Insert three rows with specific text dates in different formats.

Write a query that uses STR_TO_DATE to convert the text dates into proper DATE values.

Select the id and the converted date as signup_date.

💡 Why This Matters

🌍 Real World

Many databases store dates as text in various formats. Converting them to proper date types is essential for accurate reporting and analysis.

💼 Career

Database developers and analysts often need to clean and convert date data using functions like STR_TO_DATE to prepare data for queries and reports.

1

Create the customers table and insert data

Create a table called customers with columns id as INT and signup_date_text as VARCHAR(20). Insert these three rows exactly: (1, '12/31/2023'), (2, '2023-01-15'), and (3, '15-Jan-2023').

MySQL

-- Create the customers table and insert the three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows with the exact values.

2

Define the date format for the first date string

Create a variable or expression that holds the date format string '%m/%d/%Y' which matches the first date format '12/31/2023'.

MySQL

CREATE TABLE customers (
  id INT,
  signup_date_text VARCHAR(20)
);

INSERT INTO customers (id, signup_date_text) VALUES
(1, '12/31/2023'),
(2, '2023-01-15'),
(3, '15-Jan-2023');

-- Define the date format string for the first date
SET @format1 = '%m/%d/%Y';

Hint

Use SET @format1 = '%m/%d/%Y' to store the format string for month/day/year.

3

Write a query using STR_TO_DATE for the first date format

Write a SELECT query that selects id and converts signup_date_text to a date using STR_TO_DATE(signup_date_text, @format1). Name the converted column signup_date. Filter the rows where id = 1.

MySQL

CREATE TABLE customers (
  id INT,
  signup_date_text VARCHAR(20)
);

INSERT INTO customers (id, signup_date_text) VALUES
(1, '12/31/2023'),
(2, '2023-01-15'),
(3, '15-Jan-2023');

SET @format1 = '%m/%d/%Y';

-- Select id and converted signup_date for id 1
SELECT id, STR_TO_DATE(signup_date_text, @format1) AS signup_date FROM customers WHERE id = 1;

Hint

Use STR_TO_DATE(signup_date_text, @format1) in the SELECT clause and filter with WHERE id = 1.

4

Convert all date formats using STR_TO_DATE with CASE

Write a SELECT query that selects id and converts signup_date_text into a proper date using STR_TO_DATE. Use a CASE statement to apply these formats: '%m/%d/%Y' for id = 1, '%Y-%m-%d' for id = 2, and '%d-%b-%Y' for id = 3. Name the converted column signup_date.

MySQL

CREATE TABLE customers (
  id INT,
  signup_date_text VARCHAR(20)
);

INSERT INTO customers (id, signup_date_text) VALUES
(1, '12/31/2023'),
(2, '2023-01-15'),
(3, '15-Jan-2023');

SET @format1 = '%m/%d/%Y';

-- Select id and convert signup_date_text to date using CASE and STR_TO_DATE
SELECT id, CASE
  WHEN id = 1 THEN STR_TO_DATE(signup_date_text, '%m/%d/%Y')
  WHEN id = 2 THEN STR_TO_DATE(signup_date_text, '%Y-%m-%d')
  WHEN id = 3 THEN STR_TO_DATE(signup_date_text, '%d-%b-%Y')
END AS signup_date
FROM customers;

Hint

Use a CASE statement inside the SELECT to apply different STR_TO_DATE formats based on id.

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