How to use STR_TO_DATE function in MySQL to convert string to date

How to use the STR_TO_DATE function in MySQL to convert a string to a date

When processing dates in the database, we often need to convert strings to dates for operations and comparisons. MySQL provides the STR_TO_DATE function, which can easily convert strings to date format. This article will introduce the usage of STR_TO_DATE function in detail, with code examples.

The syntax of the STR_TO_DATE function is as follows:

STR_TO_DATE(str, format)

Among them, str is the string to be converted, and format is the string representing the date format. The following are some commonly used date format codes:

• %Y: four-digit year (such as: 2022)
• %y: two-digit year (such as: 22)
• %m: Two-digit month (01~12)
• %d: Two-digit date (01~31)
• %H: Two-digit hour (00~23)
• %i: two-digit minute (00~59)
• %s: two-digit second (00~59)

Next, we demonstrate the use of the STR_TO_DATE function through several examples:

1. Convert a string to a date

Suppose we have a string '2022-01-15' represents the date, we need to convert it to date format. You can use the following SQL statement:

SELECT STR_TO_DATE('2022-01-15', '%Y-%m-%d') AS date;

After executing the above SQL statement, you will get the date with the result 2022-01-15. This successfully converts the string to date.

2. Convert string to date and time

If the string contains date and time information, we can also use the STR_TO_DATE function to convert it to date and time format. For example, suppose we have a string '2022-01-15 10:30:25' representing date and time, and we need to convert it to date and time format. You can use the following SQL statement:

SELECT STR_TO_DATE('2022-01-15 10:30:25', '%Y-%m-%d %H:%i:%s') AS datetime;

After executing the above SQL statement, you will get the date and time as 2022-01-15 10:30:25. Conversion successful!

3. Convert strings in other formats to dates

In addition to common date formats, sometimes we also need to convert strings in other formats into dates. For example, suppose we have a string '15-Jan-2022' representing a date and we need to convert it to date format. You can use the following SQL statement:

SELECT STR_TO_DATE('15-Jan-2022', '%d-%b-%Y') AS date;

After executing the above SQL statement, you will get the date with the result 2022-01-15. By specifying the correct codename in the format parameter, we successfully converted the string to a date.

Summary:
This article introduces the method of using the STR_TO_DATE function in MySQL to convert a string to a date, and provides detailed code examples. Use the STR_TO_DATE function to easily process date strings in the database and implement various date operations and comparisons. By making reasonable use of date format codes, we can convert strings in different formats into dates to meet various needs.

Note: When using the STR_TO_DATE function, please ensure that the format of the incoming date string and the format string match, otherwise you may get incorrect results.

I hope this article will help you use the STR_TO_DATE function for date conversion in MySQL!

The above is the detailed content of How to use STR_TO_DATE function in MySQL to convert string to date. For more information, please follow other related articles on the PHP Chinese website!