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.
The date, time, or datetime values contained in str should be given in the format indicated by format. If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An illegal value also produces a warning.
The options that can be used by STR_TO_DATE(), as well as its inverse DATE_FORMAT() and the FROM_UNIXTIME() function, are:
| Option | Description |
|---|---|
%a |
Short weekday name in current locale (Variable lc_time_names). |
%b |
Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec. |
%c |
Month with 1 or 2 digits. |
%D |
Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...). |
%d |
Day with 2 digits. |
%e |
Day with 1 or 2 digits. |
%f |
Sub seconds 6 digits. |
%H |
Hour with 2 digits between 00-23. |
%h |
Hour with 2 digits between 01-12. |
%I |
Hour with 2 digits between 01-12. |
%i |
Minute with 2 digits. |
%j |
Day of the year (001-366) |
%k |
Hour with 1 digits between 0-23. |
%l |
Hour with 1 digits between 1-12. |
%M |
Full month name in current locale (Variable lc_time_names). |
%m |
Month with 2 digits. |
%p |
AM/PM according to current locale (Variable lc_time_names). |
%r |
Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'. |
%S |
Seconds with 2 digits. |
%s |
Seconds with 2 digits. |
%T |
Time in 24 hour format. Short for '%H:%i:%S'. |
%U |
Week number (00-53), when first day of the week is Sunday. |
%u |
Week number (00-53), when first day of the week is Monday. |
%V |
Week number (01-53), when first day of the week is Sunday. Used with %X. |
%v |
Week number (01-53), when first day of the week is Monday. Used with %x. |
%W |
Full weekday name in current locale (Variable lc_time_names). |
%w |
Day of the week. 0 = Sunday, 6 = Saturday. |
%X |
Year with 4 digits when first day of the week is Sunday. Used with %V. |
%x |
Year with 4 digits when first day of the week is Monday. Used with %v. |
%Y |
Year with 4 digits. |
%y |
Year with 2 digits. |
%# |
For str_to_date(), skip all numbers. |
%. |
For str_to_date(), skip all punctation characters. |
%@ |
For str_to_date(), skip all alpha characters. |
%% |
A literal % character. |
SELECT STR_TO_DATE('Wednesday, June 2, 2014', '%W, %M %e, %Y');
+---------------------------------------------------------+
| STR_TO_DATE('Wednesday, June 2, 2014', '%W, %M %e, %Y') |
+---------------------------------------------------------+
| 2014-06-02 |
+---------------------------------------------------------+
SELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y');
+--------------------------------------------------------------+
| STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y') |
+--------------------------------------------------------------+
| NULL |
+--------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: 'Wednesday23423, June 2, 2014' for function str_to_date |
+---------+------+-----------------------------------------------------------------------------------+
SELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y');
+----------------------------------------------------------------+
| STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y') |
+----------------------------------------------------------------+
| 2014-06-02 |
+----------------------------------------------------------------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/str_to_date/