Pages

Convert STRING TO DATE IN MySQL


The STR_TO_DATE() converts the str string into a date value based on the fmt format string. The STR_TO_DATE() function may return a DATE , TIME, or DATETIME value based on the input and format strings. If the input string is illegal, the STR_TO_DATE() function returns NULL.



The STR_TO_DATE() function scans the input string to match the format string. The format string may contain literal characters and format specifiers that begin with percentage (%) character. Check it out the DATE_FORMAT function for the list of format specifiers.

The STR_TO_DATE() function is very useful in data migration that involves temporal data conversion from an external format to MySQL temporal data format.


mysql> SELECT STR_TO_DATE('February 01 2019', '%M %d %Y');
Result: '2014-02-01'

mysql> SELECT STR_TO_DATE('March,8,2019', '%M,%e,%Y');
Result: '2014-03-08'

mysql> SELECT STR_TO_DATE('Friday, February 28, 2019', '%W, %M %e, %Y');
Result: '2014-02-28'

mysql> SELECT STR_TO_DATE('2019,2,28 09', '%Y,%m,%d %h');
Result: '2014-02-28 09:00:00'

mysql> SELECT STR_TO_DATE('2019,2,28 09,30,05', '%Y,%m,%d %h,%i,%s');
Result: '2014-02-28 09:30:05'

mysql> SELECT STR_TO_DATE('10,15,30', '%h,%i,%s');
Result: '10:15:30'

References:
https://dev.mysql.com/doc/refman/5.7/en/

No comments:

Post a comment