Home >MySQL >MySQL reference – functions and statements >STR_TO_DATE

STR_TO_DATE

STR_TO_DATE(str, format)

Converts a string to a date or a date and time according to the format – the opposite of DATE_FORMAT(). If the string does not match the format, it returns NULL. It does not detect a non-existent day (31 February) by itself, though.

Parameters

  • str – a string with a date, such as '3/15/2026'.
  • format – the form of the string with the same specifiers as DATE_FORMAT (%d, %m, %Y, %H, %i…). Days and months without a leading zero also pass with %d and %m.

Examples

SELECT STR_TO_DATE('3/15/2026', '%m/%d/%Y') AS date_value,
  STR_TO_DATE('15 March 2026 14:30', '%d %M %Y %H:%i') AS with_time,
  STR_TO_DATE('2026-03-15', '%m/%d/%Y') AS wrong_format;

+------------+---------------------+--------------+
| date_value | with_time           | wrong_format |
+------------+---------------------+--------------+
| 2026-03-15 | 2026-03-15 14:30:00 | NULL         |
+------------+---------------------+--------------+

Importing data from a CSV file with US dates – converting a text column to DATE:

CREATE TABLE import_csv (invoice_no VARCHAR(20), date_text VARCHAR(20));
INSERT INTO import_csv VALUES ('INV001', '1/5/2026'), ('INV002', '2/20/2026'), ('INV003', '2/31/2026');
SELECT invoice_no, date_text, STR_TO_DATE(date_text, '%m/%d/%Y') AS converted,
  DATE(CAST(STR_TO_DATE(date_text, '%m/%d/%Y') AS CHAR)) AS check_value
FROM import_csv ORDER BY invoice_no;

+------------+-----------+------------+-------------+
| invoice_no | date_text | converted  | check_value |
+------------+-----------+------------+-------------+
| INV001     | 1/5/2026  | 2026-01-05 | 2026-01-05  |
| INV002     | 2/20/2026 | 2026-02-20 | 2026-02-20  |
| INV003     | 2/31/2026 | 2026-02-31 | NULL        |
+------------+-----------+------------+-------------+

Things to watch out for

  • A string in a form other than the format returns NULL. A non-existent day of the month (31 February), however, passes through STR_TO_DATE() in a SELECT as 2026-02-31 – such a value is only revealed by converting it to a string and back, DATE(CAST(… AS CHAR)), which returns NULL. Watch out: adding an interval “fixes” it to 3 March in MySQL, while MariaDB turns it into NULL. When importing, check the rows with NULL after the conversion.
  • In INSERT and UPDATE statements in strict mode (STRICT), an invalid value may end with an error – try the conversion with SELECT first.
  • A two-digit year (%y) is completed to 1970–2069.

Related:
DATE_FORMAT()
DATE()
CAST()