Syntax:
STR_TO_DATE(str,format)

This is the inverse of the DATE_FORMAT() function. It takes a string
str and a format string format. STR_TO_DATE() returns a DATETIME value
if the format string contains both date and time parts, or a DATE or
TIME value if the string contains only date or time parts. If str or
format is NULL, the function returns NULL. If the date, time, or
datetime value extracted from str is illegal, STR_TO_DATE() returns
NULL and produces a warning.

The server scans str attempting to match format to it. The format
string can contain literal characters and format specifiers beginning
with %. Literal characters in format must match literally in str.
Format specifiers in format must match a date or time part in str. For
the specifiers that can be used in format, see the DATE_FORMAT()
function description.

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'

Scanning starts at the beginning of str and fails if format is found
not to match. Extra characters at the end of str are ignored.

mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
        -> '09:30:17'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
        -> NULL
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
        -> '09:30:17'

Unspecified date or time parts have a value of 0, so incompletely
specified values in str produce a result with some or all parts set to
0:

mysql> SELECT STR_TO_DATE('abc','abc');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'

URL: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

