The STR_TO_DATE function converts the given character string to a date/time value by interpreting it according to the specified format and operates in the opposite way to the DATE_FORMAT Function. The return value is determined by the date/time part included in the character string and it is one of the DATETIME, DATE, and TIME types.
If the string includes an invalid date/time value or the character string cannot be interpreted by applying the format specifier specified in the format, an error will be returned.
0 is not allowed in the argument value corresponding to year, month, and day; however, if 0 is inputted in every argument value corresponding to date and time, the value of DATE or DATETIME type that has 0 for every date and time value is returned as an exception. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property (see Connection Configuration of JDBC API).
STR_TO_DATE(string, format)
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
str_to_date('01,5,2013', '%d,%m,%Y')
=======================================
05/01/2013
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
str_to_date('May 1, 2013', '%M %d,%Y')
=========================================
05/01/2013
SELECT STR_TO_DATE('13:30:17','%h:%i');
str_to_date('13:30:17', '%h:%i')
========================================
01:30:00 PM
SELECT STR_TO_DATE('09:30:17 PM','%r');
str_to_date('09:30:17 PM', '%r')
=======================================
09:30:17 PM
SELECT STR_TO_DATE('0,0,0000','%d,%m,%Y');
str_to_date('0,0,0000', '%d,%m,%Y')
======================================
00/00/0000