How to convert mysql string to date

mysql can use the "STR_TO_DATE" function to convert strings to dates. The "STR_TO_DATE" function accepts two parameters: the string to be converted and the date format. By specifying the date format, the year, month, day, hour, minute and second in the string can be extracted and converted into a date and Time type. It is important to note that if you want to convert a string to a date and time, the date format must exactly match the format of the string.

Operating system for this tutorial: Windows 10 system, MySQL 8 version, Dell G3 computer.

MySQL is a commonly used relational database management system that is widely used in various web applications and data analysis. In MySQL, Date is a data type used to represent dates, and string is one of the commonly used data formats. This article will introduce how to convert a string to Date in MySQL.

In MySQL, you can use the STR_TO_DATE function to convert a string into a date. The STR_TO_DATE function accepts two parameters: the string to be converted and the date format. A date format is a string that contains specific placeholders for specifying various parts of a date, such as year, month, day, hour, minute, and second.

Here is an example that demonstrates how to convert a string to a date:

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

In the above example, we convert the string '2022-01-01' to a date and use '%Y-%m-%d' as date format. '%Y' represents a four-digit year, '%m' represents a two-digit month, and '%d' represents a two-digit date. After executing the above query, a column named date will be returned which contains the converted date.

In addition to year, month, and day, you can also include other placeholders in the date format to specify hours, minutes, and seconds. Here are some commonly used date format placeholders:

- '%H': hour in 24-hour format

- '%h': hour in 12-hour format

- '%i': minutes

- '%s': seconds

For example, to convert the string '2022-01-01 12:30:45' to date and time , you can use the following query:

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

In the above example, we use '%Y-%m-%d %H:%i:%s' as the date format to convert the string to date and time. After executing the above query, a column named datetime will be returned containing the converted date and time.

It should be noted that if you want to convert a string to a date and time, the date format must exactly match the format of the string. If the formats do not match, NULL will be returned.

In practical applications, converting strings to dates is usually used for data import and data conversion. For example, when importing data from an external data source, you may need to convert strings to dates in order to perform date comparisons and calculations in MySQL.

Summary

MySQL provides the STR_TO_DATE function to convert a string into a date. By specifying a date format, you can extract the year, month, day, hour, minute, and second from a string and convert it to date and time types. This is useful for working with date and time data and can be useful in a variety of application scenarios.

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