How STR_TO_DATE() Works in MariaDB

In MariaDB, STR_TO_DATE() is a built-in date and time function that returns a datetime value, based on the given date string and format string.

The STR_TO_DATE() function is the inverse of the DATE_FORMAT() function.

Syntax

The syntax goes like this:

STR_TO_DATE(str,format)

Where str is the date string, and format is a format string that specifies the format of the date string.

Example

Here’s an example:

SELECT STR_TO_DATE('Monday, May 24, 2021', '%W, %M %e, %Y');

Result:

+------------------------------------------------------+
| STR_TO_DATE('Monday, May 24, 2021', '%W, %M %e, %Y') |
+------------------------------------------------------+
| 2021-05-24                                           |
+------------------------------------------------------+

The format string consists of a number of format specifiers that tells MariaDB how the date in the first argument is formatted. See MariaDB Format Strings for a list of format specifiers that can be used in a format string.

In this example, the result is a date value, because the format string contains just the date parts.

Return a Datetime Value

Here’s an example that returns a datetime value:

SELECT STR_TO_DATE('May 24, 2021', '%M %e, %Y %H:%i:%S');

Result:

+---------------------------------------------------+
| STR_TO_DATE('May 24, 2021', '%M %e, %Y %H:%i:%S') |
+---------------------------------------------------+
| 2021-05-24 00:00:00                               |
+---------------------------------------------------+

In this example, the time part was added, even though the first argument contained no time part.

Here’s another example that includes a time part in the first argument:

SELECT 
STR_TO_DATE('10:30:45 on May 24, 2021', '%H:%i:%S on %M %e, %Y');

Result:

+------------------------------------------------------------------+
| STR_TO_DATE('10:30:45 on May 24, 2021', '%H:%i:%S on %M %e, %Y') |
+------------------------------------------------------------------+
| 2021-05-24 10:30:45                                              |
+------------------------------------------------------------------+

Here’s another one, where the date string uses a looser time value:

SELECT 
STR_TO_DATE('10.30pm on May 24, 2021', '%H.%ipm on %M %e, %Y');

Result:

+----------------------------------------------------------------+
| STR_TO_DATE('10.30pm on May 24, 2021', '%H.%ipm on %M %e, %Y') |
+----------------------------------------------------------------+
| 2021-05-24 10:30:00                                            |
+----------------------------------------------------------------+

Return a Time Value

Here, we change the format string to return just the time value:

SELECT STR_TO_DATE('10:30:45 on May 24, 2021', '%H:%i:%S');

Result:

+-----------------------------------------------------+
| STR_TO_DATE('10:30:45 on May 24, 2021', '%H:%i:%S') |
+-----------------------------------------------------+
| 10:30:45                                            |
+-----------------------------------------------------+

Illegal Date/Time String

Passing an illegal date string returns null with a warning.

Example:

SELECT STR_TO_DATE('Humpday, May 26, 2021', '%W, %M %e, %Y');

Result:

+-------------------------------------------------------+
| STR_TO_DATE('Humpday, May 26, 2021', '%W, %M %e, %Y') |
+-------------------------------------------------------+
| NULL                                                  |
+-------------------------------------------------------+
1 row in set, 1 warning (0.009 sec)

Let’s look at the warning:

SHOW WARNINGS;

Result:

+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: 'Humpday, May 26, 2021' for function str_to_date |
+---------+------+----------------------------------------------------------------------------+

Missing Argument

Calling STR_TO_DATE() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT STR_TO_DATE();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'STR_TO_DATE'

And another example:

SELECT STR_TO_DATE('Friday, 28 May 2021');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'STR_TO_DATE'