I have a DB where the dates are stored as varchar mm/dd/yyy (12/10/2022) But I can't figure out how to convert them to yyyy-mm-dd (2022-12-10 or 2022/12/10)
This doesn't work SELECT DATE_FORMAT("06/15/2022", "%Y-%m-%d");
P.S. I also want to issue a SQL command to change all the dates in the DB
CodePudding user response：
Here is one way to convert the dates in your database to the yyyy-mm-dd format:
UPDATE yourTable
SET yourDateColumn = STR_TO_DATE(yourDateColumn, '%m/%d/%Y')
This will update the values in the yourDateColumn to the yyyy-mm-dd format.
Alternatively, you can use the DATE_FORMAT() function to convert the dates to the yyyy-mm-dd format. Here is an example:
SELECT DATE_FORMAT(yourDateColumn, '%Y-%m-%d') AS formatted_date
FROM yourTable
CodePudding user response：
You can convert a string to a date with the function str_to_date():
select str_to_date('12/10/2022',"%m/%d/%Y");