Summary: in this tutorial, we will show you how to use the MySQL STR_TO_DATE() function to convert a string into a date time value.
Introduction to MySQL STR_TO_DATE function
The following illustrates the syntax of the
STR_TO_DATE() converts the
str string into a date value based on the
fmt format string. The
STR_TO_DATE() function may return a
DATETIME value based on the input and format strings. If the input string is illegal, the
STR_TO_DATE() function returns
STR_TO_DATE() function scans the input string to match with the format string. The format string may contain literal characters and format specifiers that begin with percentage (%) character. Check it out the DATE_FORMAT function for the list of format specifiers.
STR_TO_DATE() function is very useful in data migration that involves temporal data conversion from external format to MySQL temporal data format.
MySQL STR_TO_DATE examples
Let’s look at some examples of using
STR_TO_DATE() function to convert strings into a date and/or time values
The following statement converts a string into a
Based on the format string ‘%d, %m, %Y’, the
STR_TO_DATE() function scans the ’21,5,2013′ input string.
- First, it attempts to find a match for the
%dformat specifier, which is a day of month (01…31), in the input string. Because the number 21 matches with the
%dspecifier, the function takes 21 as the day value.
- Second, because the comma (,) literal character in the format string matches with the comma in the input string, the function continues to check the second format specifier
%m, which is month (01…12), and finds that the number 5 matches with the
%mformat specifier. It takes the number 5 as the month value.
- Third, after matching the second comma (,), the
STR_TO_DATE()function keeps finding a match for the third format specifier
%Y, which is four-digit year e.g., 2012,2013, etc., and it takes the number 2013 as the year value.
STR_TO_DATE() function ignores extra characters at the end of the input string when it parses the input string based on the format string. See the following example:
SELECT STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y');
STR_TO_DATE() sets all incomplete date values, which are not provided by the input string, to zero. See the following example:
Because the input string only provides year value, the
STR_TO_DATE() function returns a date value that has month and day set to zero.
The following example converts a time string into a
Similar to the unspecified date part, the
STR_TO_DATE() function sets unspecified time part to zero, see the following example:
The following example converts of the string into a
DATETIME value because the input string provides both data and time parts.
SELECT STR_TO_DATE('20130101 1130','%Y%m%d %h%i') ;
In this tutorial, we have shown you various examples of using the MySQL
STR_TO_DATE() function to convert strings to date and time values.