STR_TO_DATE()

Function Description

The STR_TO_DATE() function converts a string to a date or date-time type according to the specified date or time display format, synonymous with TO_DATE().

A format string can contain literal characters and format specifiers starting with %. The literal characters and format specifiers in format must match str and expressions are supported. If str cannot be parsed by format or any of the parameters are NULL, the STR_TO_DATE function returns NULL.

For format specifiers that can be used, see the DATE_FORMAT() function description.

Function Syntax

> STR_TO_DATE(str,format)

Parameter definition

Parameters	Description
str	String to format as date (input string)
format	format string to use

Example

mysql> SELECT STR_TO_DATE('2022-01-06 10:20:30','%Y-%m-%d %H:%i:%s') as result;
+--------------------------+
| result |
+--------------------------+
| 2022-01-06 10:20:30 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('09:30:17','%h:%i:%s');
+---------------------------------------+
| str_to_date(09:30:17, %h:%i:%s) |
+---------------------------------------+
| 09:30:17 |
+---------------------------------------+
1 row in set (0.00 sec)

-- format parameter supports expressions
mysql> SELECT str_to_date('2008-01-01',replace('yyyy-MM-dd','yyyy-MM-dd','%Y-%m-%d')) as result;
+----------------+
| result |
+----------------+
| 2008-01-01 |
+----------------+
1 row in set (0.00 sec)

--STR_TO_DATE function ignores the extra characters at the end of the input string str when parsing the input string str according to the format string format
mysql> SELECT STR_TO_DATE('25,5,2022 extra characters','%d,%m,%Y');
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| str_to_date(25,5,2022 extra characters, %d,%m,%Y) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2022-05-25 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('2022','%Y');
+---------------------------+
| str_to_date(2022, %Y) |
+---------------------------+
| NULL |
+------------------------------------------------------------------------------------------------------------------------------