The following illustrates the syntax of the
STR_TO_DATE()
function: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 DATE
, TIME
or DATETIME
value 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 function for 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 usingSTR_TO_DATE()
function to convert strings into a date and/or time valuesThe following statement converts a string into a
DATE
value.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%m
format 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:The
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
TIME
value: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.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.
0 comments:
Post a Comment