STR_TO_DATE
You are viewing an old version of this article. View
the current version here.
Syntax
STR_TO_DATE(str,format)
Contents
Description
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 | Microseconds 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. |
Examples
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 | +----------------------------------------------------------------+
See Also
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.