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

Need a 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';

Need a 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;

Need a 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;

Need a hint?

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