mysql的to date_MySQL STR_TO_DATE() 函数

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() function:STR_TO_DATE(str,fmt);

The STR_TO_DATE() converts the strstring into a date value based on the fmtformat string. The STR_TO_DATE()function may return a DATE, TIMEor DATETIMEvalue based on the input and format strings. If the input string is illegal, the STR_TO_DATE() function returns NULL.

The 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 functionfor the list of format specifiers.

The 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 DATEvalue.SELECT STR_TO_DATE('21,5,2013','%d,%m,%Y');

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 %d format specifier, which is a day of month (01…31), in the input string. Because the number 21 matches with the %d specifier, 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.

The 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');

The STR_TO_DATE() sets all incomplete date values, which are not provided by the input string, to zero. See the following example:SELECT STR_TO_DATE('2013','%Y');

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 TIMEvalue:SELECT STR_TO_DATE('113005','%h%i%s');

Similar to the unspecified date part, the STR_TO_DATE() function sets unspecified time part to zero, see the following example:SELECT STR_TO_DATE('11','%h');

The following example converts of the string into a DATETIMEvalue 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.