I have a date field (tinytext) holding date information in format of "dd-mm-yy" e.g 07-01-90. Using a mysql query I want to change it to yyyy-mm-dd date format. I tried the code below but nothing happens.
mysql_query("UPDATE Table SET date=STR_TO_DATE('date','%Y,%m,%d')");
You're using the correct function STR_TO_DATE(str,format)
to achieve the goal, but you're making two mistakes:
format
argument does not match the string expression format. You said it's in dd-mm-yy
format while you passed %Y,%m,%d
(comma separated) to the format
argument. The query will return a "incorrect datetime value" error. You should use %d-%m-%Y
.So, summarizing:
mysql_query("UPDATE `Table` SET `date` = STR_TO_DATE(`date`, '%d-%m-%Y')");
mysql_query("ALTER TABLE `Table` CHANGE COLUMN `date` `date` DATE");
Additionally, consider switching to the recommended PDO extension in place of old and slowly deprecated mysql
extension.
Error in your query
is STR_TO_DATE(date, '%Y-%m-%d')
mysql_query("UPDATE Table SET date=STR_TO_DATE(date,'%Y-%m-%d')");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With