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 , for any “x ” not listed above |