Search code examples
str-to-date

php/mysql routine to help me extract all dates strings stored in a column as text dd/mm/yyyy into another fresh column with mysql date datatype

i am new here and i need help with writing a php/mysql routine to help me extract all dates strings stored in a column as text in this format dd/mm/yyyy into another fresh column with mysql date datatype in the format yyyy-mm-dd.

i have tried to query through the table using a date range and it seems not to work, so i have resolve to change th already store dates into the proper mysql format to enable me query with date easily.

indeed i am most greatful of any assistance i get.

thanks

Handyx

Solution

  • First of all you probably shouldn't be doing what your doing, convert the date on the go. Nevertheless here's the SQL query to do what you want to do

    USE `DATABASE`;
UPDATE `TABLE_NAME` SET `TABLE_NAME.NEW_COLUMN` = DATE_FORMAT(
STR_TO_DATE(`TABLE_NAME.OLD_COLUMN`,'%d/%m/%Y'), '%Y-%m-%d');