How to Convert String to DATE in MariaDB

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

STR_TO_DATE() converts a character string to a DATE, TIME, or DATETIME value using a format mask.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does STR_TO_DATE() do?

STR_TO_DATE() parses a text value according to a supplied format string and returns a DATE, TIME, or DATETIME. The function is ideal when raw data arrives as VARCHAR and you must store or filter by real date types.

How do I convert a string to DATE?

Basic usage

Call STR_TO_DATE(string, format). MariaDB matches the format specifiers (e.g., %Y, %m, %d) to the string’s structure and returns a DATE. If the pattern or value is invalid, the result is NULL and the statement raises a warning.

Using ecommerce data

Suppose Orders.order_date_raw is VARCHAR(20) like ‘2024-06-15’. A nightly job can convert and store it in a proper DATE column so indexes and comparisons remain fast.

What is the exact syntax?

STR_TO_DATE(string, format_mask) → DATE | DATETIME | TIME

Common specifiers: %Y (4-digit year), %m (numeric month), %d (day), %H (24-hour), %i (minute), %s (second). Combine them to mirror the incoming text.

What are real-world examples?

Example: inserting an order

INSERT INTO Orders (customer_id, order_date, total_amount)SELECT id, STR_TO_DATE('15/06/2024', '%d/%m/%Y'), 199.99FROM Customers WHERE email = 'alice@example.com';

Example: filtering by signup date

SELECT name, emailFROM CustomersWHERE created_at >= STR_TO_DATE('2024-01-01', '%Y-%m-%d');

What are best practices?

Match format masks exactly, store converted values in DATE/DATETIME columns, and wrap conversion inside INSERT … SELECT or UPDATE so that data is normalized once, not at query time.

What mistakes should I avoid?

Mismatched format mask: Using ‘%Y-%d-%m’ for ‘2024-06-15’ swaps day and month, returning NULL. Align each specifier.

Ignoring time zones: STR_TO_DATE() returns naive DATETIME. Convert or store as UTC if the application is timezone-aware.

Frequently asked questions

Can I cast without STR_TO_DATE()?

Yes, CAST('2024-06-15' AS DATE) works for ISO-formatted strings, but fails if the layout deviates. STR_TO_DATE() is safer for mixed formats.

Does STR_TO_DATE() use locale settings?

No, the function relies solely on the explicit format string, ensuring predictable results across servers.

How can I handle invalid rows?

Enable STRICT mode to make conversion errors throw and capture bad data early; otherwise, check for NULL results during ETL.

Why How to Convert String to DATE in MariaDB is important

How to Convert String to DATE in MariaDB Example Usage


UPDATE Orders
SET order_date = STR_TO_DATE(order_date_raw, '%Y-%m-%d')
WHERE order_date IS NULL
  AND order_date_raw REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';

How to Convert String to DATE in MariaDB Syntax


STR_TO_DATE(string, format_mask)
-- Example masks
-- '%Y-%m-%d'  → 2024-06-15
-- '%d/%m/%Y'  → 15/06/2024
-- '%Y%m%d'    → 20240615

-- Cast variant (only ISO strings)
CAST(string AS DATE)

-- Ecommerce context
-- Convert raw text date while loading orders
INSERT INTO Orders (customer_id, order_date, total_amount)
SELECT c.id,
       STR_TO_DATE(o.order_date_raw, '%Y-%m-%d'),
       o.total_amount
FROM   StagingOrders o
JOIN   Customers c ON c.email = o.email;

Common Mistakes

Using the wrong format specifier order (e.g., %d/%m/%Y for a ‘2024-06-15’ string). MariaDB cannot reconcile day-month swap, so it returns NULL. Use %Y-%m-%d instead.
Leaving date conversion in SELECT queries rather than materializing in a DATE column. This prevents index use and slows every read. Run one UPDATE/INSERT to store the converted value upfront.