How Can I Convert dd/mm/yyyy Strings to MySQL Dates in SELECT Queries?

Converting Strings to Dates in MySQL SELECT Queries

Many systems store dates as strings in the format dd/mm/yyyy. To utilize date functions like DATE_FORMAT, it's necessary to convert these strings to the MySQL-compatible format yyyy-mm-dd.

To accomplish this conversion, the STR_TO_DATE function can be employed:

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

This will return a datetime datatype. To ensure the desired format, DATE_FORMAT can be used as follows:

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

If the original column's datatype cannot be altered, it's recommended to create a view that performs the STR_TO_DATE conversion to a DateTime data type.

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