STR_TO_DATE
Converts a string to a date, time or datetime using the given format.
STR_TO_DATE(string, format)Parameters
- stringVARCHAR
- String with a date or time
- formatVARCHAR
- Format of the string, specifiers as in DATE_FORMAT
Return value
A datetime if the format has both date and time parts, otherwise a date or time; NULL if the string does not match the format, the day is greater than 31 or the month is greater than 12
Examples
Parsing a DD.MM.YYYY date
SELECT STR_TO_DATE('16.06.2022', '%d.%m.%Y') AS parsed;Datetime from text
SELECT STR_TO_DATE('November 13, 1998 14:05', '%M %d, %Y %H:%i') AS parsed;String not matching the format
SELECT STR_TO_DATE('2022/06/16', '%d.%m.%Y') AS parsed;Details
The format specifiers are the same as in DATE_FORMAT. Parsing starts at the beginning of the string: the text between specifiers must match the format, and extra characters at the end of the string are ignored.
Whether the date exists is not checked: the day is only checked against the range from 1 to 31. STR_TO_DATE('31.02.2022', '%d.%m.%Y') returns the non-existent date 2022-02-31, not NULL. To filter out such dates, add INTERVAL 0 DAY to the result — the extra days move to the next month (2022-03-03) — and compare it with the source string using DATE_FORMAT: for a non-existent date the strings will differ.