How to Convert 'dd/mm/yyyy' Date Strings to 'yyyy-mm-dd' in MySQL?

Converting Date Strings in MySQL

When working with databases, it's common to encounter dates stored as strings in non-standard formats. In this instance, suppose you have dates stored as "dd/mm/yyyy" strings. To manipulate these dates effectively, you may want to convert them to the standardized "yyyy-mm-dd" format.

Solution:

MySQL provides a powerful function called STR_TO_DATE that allows you to parse date strings into datetime data types. To achieve your desired conversion, use the following syntax within a SELECT query:

STR_TO_DATE(t.datestring, '%d/%m/%Y')

Where "t" represents the table name and "datestring" is the column containing the date strings. This will convert the string into a datetime datatype.

To ensure the output is in the "yyyy-mm-dd" format, you can utilize the DATE_FORMAT function:

DATE_FORMAT(STR_TO_DATE(t.datestring, '%d/%m/%Y'), '%Y-%m-%d')

Alternative Approach:

If modifying the data type of the original column is not an option, consider creating a view using the STR_TO_DATE function to convert the string column to a datetime column:

CREATE VIEW my_view AS
SELECT t.id, t.other_columns, STR_TO_DATE(t.datestring, '%d/%m/%Y') AS converted_date
FROM t

You can then use this view to access the dates in the standardized format.

The above is the detailed content of How to Convert 'dd/mm/yyyy' Date Strings to 'yyyy-mm-dd' in MySQL?. For more information, please follow other related articles on the PHP Chinese website!