How to Convert String to Date in MySQL

Galaxy Glossary

How do I convert a string to a DATE in MySQL?

STR_TO_DATE turns a string into a DATE, DATETIME, or TIME using a supplied format mask.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does STR_TO_DATE do?

STR_TO_DATE() parses a character string according to a format mask and returns a DATE, DATETIME, or TIME. Use it when you import CSV files, fix bad data, or compare dates stored as text.

What is the exact syntax?

Use STR_TO_DATE(string, format). The format uses the same specifiers as DATE_FORMAT, such as %Y for four-digit year or %d for day of month.See full syntax below.

How do I convert order dates from CSV imports?

You often bulk-load Orders where order_date is text like “04/30/2024”. Run an UPDATE with STR_TO_DATE to populate a real DATE column so indexes and date math work.

Can I convert and insert in one step?

Yes.In an INSERT … SELECT or LOAD DATA, wrap the text column in STR_TO_DATE so the value lands correctly typed, eliminating a follow-up UPDATE.

What are best practices for string to date conversion?

Always match the format string to the incoming data exactly; test with SELECT before UPDATE; store as DATE or DATETIME, not VARCHAR; add CHECK constraints to avoid future bad loads.

What mistakes should I avoid?

Mis-matched format masks and implicit timezone assumptions are the top pitfalls. See details below.

.

Why How to Convert String to Date in MySQL is important

How to Convert String to Date in MySQL Example Usage


-- Convert string dates while moving data into production table
INSERT INTO Orders (id, customer_id, order_date, total_amount)
SELECT id,
       customer_id,
       STR_TO_DATE(order_date_text, '%m/%d/%Y'),
       total_amount
FROM   Orders_staging;

How to Convert String to Date in MySQL Syntax


STR_TO_DATE(str, format)

# Common specifiers
%Y – four-digit year  |  %y – two-digit year
%m – month (01-12)    |  %d – day of month (01-31)
%H – hour (00-23)     |  %i – minutes (00-59)  |  %s – seconds (00-59)

-- Example: convert US-style date string in Orders import
UPDATE Orders_import
SET order_date = STR_TO_DATE(order_date_text, '%m/%d/%Y');

Common Mistakes

Using the wrong format mask: A string '2024-05-01' needs '%Y-%m-%d', not '%d/%m/%Y'. Mismatching causes NULL results. Always test with SELECT and LENGTH checks.
Leaving text in VARCHAR columns: Queries on VARCHAR dates are slow and error-prone. Always convert to DATE/DATETIME and add indexes for range searches.

Frequently Asked Questions (FAQs)

Can STR_TO_DATE handle time zones?

No. STR_TO_DATE only parses; it does not shift time zones. Store as UTC or use CONVERT_TZ afterward.

What happens on invalid input?

MySQL returns NULL and, if sql_mode includes STRICT, raises an error. Use WHERE order_date IS NULL after conversion to catch bad rows.

Is STR_TO_DATE deterministic?

Yes. Given the same string and format, it always returns the same value. This allows generated columns and functional indexes.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie