How Can I Convert dd/mm/yyyy Dates to YYYY-mm-dd Format in MySQL?

Converting String Dates in MySQL to YYYY-MM-DD Format

In many systems, dates are often stored as strings in the format dd/mm/yyyy. If you need to convert these dates to the YYYY-mm-dd format for further manipulation, MySQL provides a straightforward solution.

The STR_TO_DATE Function

MySQL offers the STR_TO_DATE function to parse string dates into datetime datatypes. To convert a string date like '15/03/2023' to a datetime value, use the following syntax:

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

Formatting the Converted Date

Once you have converted the string to a datetime datatype, you can use the DATE_FORMAT function to ensure it appears in the desired YYYY-mm-dd format:

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

Alternative Solution Using a View

If you cannot change the data type of the original column, consider creating a view that utilizes the STR_TO_DATE conversion:

CREATE VIEW my_view AS
SELECT STR_TO_DATE(datestring, '%d/%m/%Y') AS converted_date
FROM table_name;

Usage

SELECT converted_date FROM my_view;

Using the view will provide the converted dates in the desired format without needing to modify the original column.

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