The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-12-22. For the latest, interactive version please visit https://mariadb.com/kb/.

STR_TO_DATE

Syntax

STR_TO_DATE(str,format)

Contents

  1. Syntax
  2. Description
  3. Examples
  4. See Also

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.

Under specific SQL_MODE settings an error may also be generated if the str isn't a valid date:

The options that can be used by STR_TO_DATE(), as well as its inverse DATE_FORMAT() and the FROM_UNIXTIME() function, are:

OptionDescription
%aShort weekday name in current locale (Variable lc_time_names).
%bShort 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.
%cMonth with 1 or 2 digits.
%DDay with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%dDay with 2 digits.
%eDay with 1 or 2 digits.
%fMicroseconds 6 digits.
%HHour with 2 digits between 00-23.
%hHour with 2 digits between 01-12.
%IHour with 2 digits between 01-12.
%iMinute with 2 digits.
%jDay of the year (001-366)
%kHour with 1 digits between 0-23.
%lHour with 1 digits between 1-12.
%MFull month name in current locale (Variable lc_time_names).
%mMonth with 2 digits.
%pAM/PM according to current locale (Variable lc_time_names).
%rTime in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%SSeconds with 2 digits.
%sSeconds with 2 digits.
%TTime in 24 hour format. Short for '%H:%i:%S'.
%UWeek number (00-53), when first day of the week is Sunday.
%uWeek number (00-53), when first day of the week is Monday.
%VWeek number (01-53), when first day of the week is Sunday. Used with %X.
%vWeek number (01-53), when first day of the week is Monday. Used with %x.
%WFull weekday name in current locale (Variable lc_time_names).
%wDay of the week. 0 = Sunday, 6 = Saturday.
%XYear with 4 digits when first day of the week is Sunday. Used with %V.
%xYear with 4 digits when first day of the week is Monday. Used with %v.
%YYear with 4 digits.
%yYear 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

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.