How to convert a string to date in MySQL?
System Design practice on Codemia
Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.
Introduction
Date strings arrive in many formats from CSV files, APIs, and legacy systems, while MySQL date columns need typed values for reliable filtering and indexing. Converting strings correctly is essential to avoid silent nulls and broken reports. The main tool is STR_TO_DATE, paired with validation and cleanup queries.
Convert Date Strings With STR_TO_DATE
STR_TO_DATE parses a string according to a format pattern and returns a temporal value. If parsing fails, MySQL returns NULL, which is useful for quality checks.
Basic conversion:
Common format tokens:
%Yfour-digit year%mmonth number%dday of month%Hhour in twenty-four format%iminutes%sseconds
Always match the exact input format. Even small mismatches, such as swapped day and month positions, produce incorrect values or null output.
Clean and Insert Converted Data
When importing text-heavy sources, stage raw strings first, then convert into typed columns. This separates parsing from ingestion and makes error handling easier.
This pattern inserts only valid rows. Invalid records remain in staging for audit and remediation.
Detect Invalid or Ambiguous Inputs
Some string formats are ambiguous across locales. For example, 04-05-2026 can mean two different dates depending on convention. Use strict import contracts and explicit format patterns per source.
Find invalid rows:
If data has mixed formats, normalize upstream before database insertion. Attempting many format fallbacks inside one SQL statement quickly becomes brittle and hard to maintain.
Convert Existing Text Columns In Place
If a production table stores dates as text, migrate carefully with a new typed column first. This reduces risk and allows phased validation.
After validation, update application code to read event_date, then remove the old text column in a controlled deployment.
Index and Query Typed Dates Correctly
Once converted, typed date columns support efficient range queries and date functions. Avoid converting string values on every query, which blocks index usage and increases latency.
Good query pattern:
For reports requiring display formatting, keep raw typed storage and format only at query output or application layer.
Storage should stay typed and canonical, while display can be localized as needed.
Common Pitfalls
- Using the wrong format mask with
STR_TO_DATE, causing nulls or swapped values. - Storing dates as text long-term, which hurts indexing and consistency.
- Ignoring invalid parse results instead of explicitly filtering or logging them.
- Mixing locale-specific formats in one source pipeline without normalization.
- Running conversion logic inside every analytical query instead of one-time migration.
Summary
- Use
STR_TO_DATEwith exact format strings to parse date text safely. - Stage raw strings before inserting into typed date columns.
- Treat null parse results as data quality signals, not harmless noise.
- Migrate legacy text columns through phased typed-column backfills.
- Keep storage typed and apply display formatting only at output time.
Related reading
- How to convert a string to ObjectId in nodejs mongodb native driver?
- How to convert all tables from MyISAM into InnoDB?
- How to convert an entire MySQL database characterset and collation to UTF-8?
- How to convert index of a pandas dataframe into a column
- How to convert java.sql.timestamp to LocalDate java8 java.time?
- How to convert java.util.Date to java.sql.Date?
- How to convert result table to JSON array in MySQL
- How to convert SQL Query result to PANDAS Data Structure?

System Design Fundamentals
Build a strong foundation in designing scalable, reliable distributed systems.
View the courseTrack what you have practised
A free account saves your progress, solutions and study plan across every problem on Codemia.
System Design practice on Codemia
Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.