How to convert mysql string date

In the MySQL database, date data is a very common data type. When we need to calculate and compare date data, we need to convert the date data. This article will introduce the method of converting string date in MySQL.

1. String date format

In MySQL, there are many string date formats, the most common ones are the following:

• YYYY-MM- DD
• YYYY/MM/DD
• YYYYMMDD
• MM/DD/YYYY
• DD/MM/YYYY

Among them, YYYY represents the year, MM represents the month, and DD represents the date.

2. Convert to date type

In MySQL, you can use the "STR_TO_DATE" function to convert a string date to a date type.

For example, to convert the string "2021-10-01" to a date type, you can use the following statement:

SELECT STR_TO_DATE('2021-10-01', '%Y-%m-%d');

Among them, "%Y-%m-%d" is the date The format needs to be modified according to the actual situation.

3. Convert to Unix timestamp

In MySQL, you can also convert a string date to a Unix timestamp. A Unix timestamp is the number of seconds since 0:00:00 on January 1, 1970.

Use the "UNIX_TIMESTAMP" function to convert a string date into a Unix timestamp.

For example, to convert the string "2021-10-01" to a Unix timestamp, you can use the following statement:

SELECT UNIX_TIMESTAMP('2021-10-01');

4. Convert to string type

In In MySQL, you can convert date type or Unix timestamp to string type. Use the "DATE_FORMAT" function to convert the date type to the string type, and use the "FROM_UNIXTIME" function to convert the Unix timestamp to the string type.

For example, to convert the date type "2021-10-01" to a string type, you can use the following statement:

SELECT DATE_FORMAT('2021-10-01', '%Y-%m-%d');

Among them, "%Y-%m-%d" is the The converted string date format needs to be modified according to the actual situation.

To convert Unix timestamp to string type, you can use the following statement:

SELECT FROM_UNIXTIME(1633046400, '%Y-%m-%d');

Among them, "1633046400" is the Unix timestamp to be converted, '%Y-%m-%d' It is the string date format to be converted and needs to be modified according to the actual situation.

5. Summary

This article introduces the conversion method of string date in MySQL, including conversion to date type, conversion to Unix timestamp and conversion to string type. In practical applications, different conversion methods can be selected according to needs.

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