Convert string to date using MySQL's STR_TO_DATE function

Use MySQL's STR_TO_DATE function to convert a string into a date

In the MySQL database, we often encounter the need to convert a string into a date. At this time, we can use the STR_TO_DATE function provided by MySQL to achieve this function. The STR_TO_DATE function can convert a string in a specified format into a date type and supports multiple date formats.

Now, let’s look at a specific example. Suppose there is a table named orders, one of which is order_date, which stores a string type date in the format of "YYYY-MM-DD". We now need to convert these string dates into date types in order to perform date calculations and comparisons.

First, we need to create a sample table orders and insert some sample data.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date VARCHAR(10)
);

INSERT INTO orders (order_date) VALUES
    ('2020-01-01'),
    ('2020-02-15'),
    ('2020-03-30'),
    ('2020-04-10');

Next, we can use the STR_TO_DATE function to convert the string date of the order_date column to a date type. The specific operations are as follows:

SELECT id, STR_TO_DATE(order_date, '%Y-%m-%d') AS order_date
FROM orders;

In the above code, we use the STR_TO_DATE function to convert the string date of the order_date column into a date type. Among them, '%Y-%m-%d' is the date format pattern string, used to specify the format of the date in the order_date column. In the pattern string, %Y represents the year, %m represents the month, and %d represents the date.

After executing the above query statement, you will get the following results:

+----+------------+
| id | order_date |
+----+------------+
|  1 | 2020-01-01 |
|  2 | 2020-02-15 |
|  3 | 2020-03-30 |
|  4 | 2020-04-10 |
+----+------------+

You can see that the string date in the order_date column has been successfully converted to date type.

In addition to the common date format '%Y-%m-%d', the STR_TO_DATE function also supports multiple date formats. The following are some commonly used date format pattern strings and their corresponding meanings:

• '%Y-%m-%d': Date represented in "YYYY-MM-DD" format
• '%Y/%m/%d': Date expressed in "YYYY/MM/DD" format
• '%Y%m%d': Date expressed in "YYYYMMDD" format
• '%Y%m': Date expressed in "YYYYMM" format
• '%Y-%m-%d %H:%i:%s': Date expressed in "YYYY- Date and time represented in MM-DD HH:MM:SS" format

It should be noted that when converting the date, the STR_TO_DATE function will verify it according to the specified format mode. If the string date to be converted does not match the specified format pattern, NULL will be returned. Therefore, when using the STR_TO_DATE function, it is important to ensure that the format pattern is correct.

To sum up, MySQL’s STR_TO_DATE function can easily convert a string date into a date type. By specifying the appropriate format pattern, we can flexibly handle strings in various date formats. In daily data processing, this function is a very practical tool.

Reference materials:

• STR_TO_DATE function documentation: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html# function_str-to-date

The above is the detailed content of Convert string to date using MySQL's STR_TO_DATE function. For more information, please follow other related articles on the PHP Chinese website!