Converting strings to dates in MySQL mainly uses the STR_TO_DATE() function. 1. The basic usage of this function is STR_TO_DATE(str, format), which is used to parse the string str into a date or time value in the specified format format; 2. The return value type can be DATE, DATETIME or TIME, depending on the input string and format; 3. If the format does not match, NULL or an error result may be returned, so the format must be strictly corresponding; 4. Common formats include: %Y (four-digit year), %y (two-digit year), %m (month), %d (date), %H (hour), %i (minute), %s (seconds), etc.; 5. For strings with English month or week names, formats such as %M (full month name), %b (abbreviated month name), %W (full week name), %a (abbreviated week name), etc. can be used; 6. When using it, you need to pay attention to case matching, legal date range (such as the month does not exceed 12, and the date does not exceed the maximum number of days of the month); 7. In addition to query, STR_TO_DATE() is often used in INSERT and UPDATE operations, converting strings to standard date types to save to the database.

In MySQL, converting strings to dates mainly uses the STR_TO_DATE() function. This function is very practical, especially common when importing data or processing date formats entered by users.

Basic usage

The function of STR_TO_DATE(str, format) is to parse the string str into a date or time value according to the specified format format. Its return value type can be DATE, DATETIME, or TIME, depending on the incoming string and format.

Let's give a simple example:

 SELECT STR_TO_DATE('2024-03-15', '%Y-%m-%d');

The result is 2024-03-15 , the type is DATE.

It should be noted that if the format does not match, NULL or an incorrect result may be returned, so the format must be strictly corresponding.

Description of common format characters

Here are some commonly used formats to facilitate you to construct correct expressions based on different strings:

• %Y : four-digit year, such as 2024
• %y : a double-digit year, such as 24 (note that it will automatically complete to 2024)
• %m : Month (00-12)
• %d : Date (00-31)
• %H : hours (00-23)
• %i : minutes (00-59)
• %s or %S : seconds (00-59)

for example:

 SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y'); -- Result: 2024-03-15
SELECT STR_TO_DATE('03:25:00', '%H:%i:%s'); -- Result: 03:25:00

If the string format you encounter is messy, such as 'March 15, 2024' , you can also write it like this:

 SELECT STR_TO_DATE('March 15, 2024', '%M %d, %Y');

Process date formats with text

Sometimes the string contains the English month or week name, and then the corresponding formatting character needs to be used to correctly identify:

• %M : Complete month name (January to December)
• %b : The abbreviated month name (Jan to Dec)
• %W : Full name of the week (Sunday to Saturday)
• %a : The abbreviated week name (Sun to Sat)

For example:

 SELECT STR_TO_DATE('Friday, April 5, 2024', '%W, %M %d, %Y');
-- Result: 2024-04-05

Although these formats are common in daily life, you should pay attention to whether the case matches in actual use, otherwise it may not be recognized.

Used in INSERT and UPDATE

In addition to queries, one of the most common uses STR_TO_DATE() is to convert the string into a date and save it to the database when inserting or updating records.

For example, there is a table orders , where a field is order_date DATE , and the data you want to insert is in the form of a string:

 INSERT INTO orders (order_date)
VALUES (STR_TO_DATE('2024-03-15', '%Y-%m-%d'));

This ensures that the standard DATE type is inserted, not the string.

Things to note

• If the contents in the string cannot be parsed correctly, the function returns NULL.
• Different locale settings may cause the English month or week name recognition to fail.
• The date range must be legal, such as the month cannot exceed 12, and the number of days cannot exceed the maximum number of days of the month.

For example:

 SELECT STR_TO_DATE('2024-02-30', '%Y-%m-%d'); -- Return NULL because there is no 30th in February

Basically that's it. Mastering the usage of STR_TO_DATE() will be much easier when dealing with various date strings. As long as the format is right, there will be basically no errors.

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