Convert Strings to Dates in MySQL

In this tutorial, we explain how to convert strings to dates in MySQL using the STR_TO_DATE() function, with examples that demonstrate various date formats and practical applications.

In MySQL, converting strings to dates is essential when working with data stored in text format that needs to be treated as date values. For example, date values stored as VARCHAR fields or received in different formats from external sources often require conversion to proper date types for accurate comparisons, calculations, and formatting.

Using STR_TO_DATE() for String-to-Date Conversion

The STR_TO_DATE() function in MySQL converts a string to a date type, based on a specified format. The syntax is:

</>
Copy
STR_TO_DATE(string, format);

Here, string is the text you want to convert, and format is the format of the date in the string, such as '%Y-%m-%d' for YYYY-MM-DD. MySQL provides a range of format specifiers, like %d for day, %m for month, and %Y for year.

Setting Up a Sample Table

To demonstrate STR_TO_DATE(), we’ll create a events table with a string_date column that stores dates as strings. Follow these steps to set up the table and insert sample data.

1. First, create the events table:

</>
Copy
CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(50),
    string_date VARCHAR(20)
);

2. Insert sample data with various date formats:

</>
Copy
INSERT INTO events (event_name, string_date)
VALUES 
('New Year', '2024-01-01'),
('Independence Day', '07-04-2024'),
('Company Meeting', '2024/09/15'),
('Project Deadline', '15/10/2024');

With this setup, we can use STR_TO_DATE() to convert these strings into date values.

Convert Strings to Dates in MySQL - Setup for Examples

Examples of Converting Strings to Dates

Let’s look at examples that convert different date string formats into MySQL date types using STR_TO_DATE().

1. Converting a YYYY-MM-DD Format

To convert a string in YYYY-MM-DD format to a date:

</>
Copy
SELECT event_name, string_date,
       STR_TO_DATE(string_date, '%Y-%m-%d') AS formatted_date
FROM events
WHERE string_date LIKE '2024-%';

This query converts string_date values like “2024-01-01” into proper date values for further date calculations and comparisons.

Convert Strings to Dates in MySQL Example - Converting a YYYY-MM-DD Format

2. Converting DD-MM-YYYY Format

To convert strings in DD-MM-YYYY format to dates:

</>
Copy
SELECT event_name, string_date,
       STR_TO_DATE(string_date, '%d-%m-%Y') AS formatted_date
FROM events
WHERE string_date LIKE '%-%-%';

This query converts dates in DD-MM-YYYY format, such as “07-04-2024”, into a date value.

Convert Strings to Dates in MySQL Example - Converting DD-MM-YYYY Format

3. Converting YYYY/MM/DD Format

To convert a string in YYYY/MM/DD format:

</>
Copy
SELECT event_name, string_date,
       STR_TO_DATE(string_date, '%Y/%m/%d') AS formatted_date
FROM events
WHERE string_date LIKE '2024/%';

This query converts string_date values like “2024/09/15” into date format, useful for performing date calculations.

Convert Strings to Dates in MySQL Example - Converting YYYY/MM/DD Format

4. Converting DD/MM/YYYY Format

To handle dates stored in DD/MM/YYYY format:

</>
Copy
SELECT event_name, string_date,
       STR_TO_DATE(string_date, '%d/%m/%Y') AS formatted_date
FROM events
WHERE string_date LIKE '%/%/%';

This query converts dates like “15/10/2024” into date format.

Convert Strings to Dates in MySQL Example - Converting DD/MM/YYYY Format

Using STR_TO_DATE() allows you to standardize and manipulate date strings in MySQL, enabling easy data management and accurate date-based queries.