STR_TO_DATE() Examples – MySQL

Posted on by Ian

The MySQL STR_TO_DATE() function allows you to build a date value from the various date parts.

When using this function, you provide a string of the various date parts, and a second argument that specifies the format that the date is provided in.

Syntax

The syntax goes like this:

STR_TO_DATE(str,format)

Where str is the string that contains the date parts, and format is the format string (determines how the str argument is formatted).

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT STR_TO_DATE('31,12,1999','%d,%m,%Y');

Result:

+--------------------------------------+
| STR_TO_DATE('31,12,1999','%d,%m,%Y') |
+--------------------------------------+
| 1999-12-31                           |
+--------------------------------------+

Example 2 – Order of the Format String

The format string must match the values provided in the date string. For example, you can’t do the following:

SELECT STR_TO_DATE('31,12,1999','%m,%d,%Y');

Result:

+--------------------------------------+
| STR_TO_DATE('31,12,1999','%m,%d,%Y') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

The reason this didn’t work is because we’re trying to force the month to have a value of 31, but there are only 12 months in a year. So this would result in an invalid date value.

Therefore you’ll need to be careful when using this function, especially when the day value is 12 or less, otherwise you could end up with the month and day values being swapped around without noticing it.

For example, like this:

SELECT 
    STR_TO_DATE('07,12,1999','%d,%m,%Y'),
    STR_TO_DATE('07,12,1999','%m,%d,%Y');

Result:

+--------------------------------------+--------------------------------------+
| STR_TO_DATE('07,12,1999','%d,%m,%Y') | STR_TO_DATE('07,12,1999','%m,%d,%Y') |
+--------------------------------------+--------------------------------------+
| 1999-12-07                           | 1999-07-12                           |
+--------------------------------------+--------------------------------------+

In this case, we don’t get a NULL value like in the previous example, because both 07 and 12 could be either a day or a month.

Example 3 – Matching the Format String

Even if the format string is in the correct order, it must also match the format of the date parts provided in the first argument.

So for example, you can’t do this:

SELECT STR_TO_DATE('31 Dec, 1999','%d,%m,%Y');

Result:

+----------------------------------------+
| STR_TO_DATE('31 Dec, 1999','%d,%m,%Y') |
+----------------------------------------+
| NULL                                   |
+----------------------------------------+

In this case, we’d need to do something like this:

SELECT STR_TO_DATE('31 Dec, 1999','%d %M, %Y');

Result:

+-----------------------------------------+
| STR_TO_DATE('31 Dec, 1999','%d %M, %Y') |
+-----------------------------------------+
| 1999-12-31                              |
+-----------------------------------------+

Example 4 – Using the GET_FORMAT() Function

You can also pass the GET_FORMAT() function as the second argument.

Here’s an example.

SELECT STR_TO_DATE('12.07.1999', GET_FORMAT(DATE, 'USA'));

Result:

+----------------------------------------------------+
| STR_TO_DATE('12.07.1999', GET_FORMAT(DATE, 'USA')) |
+----------------------------------------------------+
| 1999-12-07                                         |
+----------------------------------------------------+

Example 5 – Datetime Values

You can also use this function to return a time or datetime value.

Here’s an example using a datetime value.

SELECT STR_TO_DATE('31/12/1999 09:30:17','%d/%m/%Y %H:%i:%s');

Result:

+--------------------------------------------------------+
| STR_TO_DATE('31/12/1999 09:30:17','%d/%m/%Y %H:%i:%s') |
+--------------------------------------------------------+
| 1999-12-31 09:30:17                                    |
+--------------------------------------------------------+

Format String Specifiers

The following specifiers can be used in the format string. In addition to these, you can also use literal strings in the format string.

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal % character
%x x, for any “x” not listed above