How to Convert MySQL Date Strings (dd/mm/yyyy) to yyyy-mm-dd Format?

Converting MySQL Date Strings to yyyy-mm-dd in Select Queries

To convert dates stored as strings in the dd/mm/yyyy format in MySQL, you can leverage the STR_TO_DATE() function to parse the string and transform it into a datetime datatype. This conversion is crucial for subsequently applying date manipulation functions like DATE_FORMAT().

Solution

To convert the date string to the desired yyyy-mm-dd format, you can use the following formula in a SELECT query:

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

Breaking down the formula:

• STR_TO_DATE(t.datestring, '%d/%m/%Y'): Parses the date string in the dd/mm/yyyy format and converts it to a datetime datatype.
• DATE_FORMAT(): Formats the converted datetime value into the desired yyyy-mm-dd format.

Example Usage

SELECT DATE_FORMAT(STR_TO_DATE('25/03/2023', '%d/%m/%Y'), '%Y-%m-%d') AS formatted_date;

Output:

2023-03-25

Alternative Approach: Creating a View

If you cannot modify the datatype of the original column, consider creating a view that converts the date strings to datetime values using STR_TO_DATE(). This approach allows you to perform queries on the converted values without altering the underlying table.

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

Then, you can query the converted_dates view to retrieve the converted date values directly:

SELECT converted_date
FROM converted_dates
WHERE id = 1;

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