0

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

handyx
  • 11
  • 2
  • Post some more info, i.e. some of your table structure and data, and you'll get some accurate answers. Is there only a single date in the current date field or are there strings of multiple dates in a single column that you need to target? @handyx – J Charles Jul 21 '14 at 01:05
  • ok, i will past the query. do you mean the same format for the dates as they are in the table presently? thanks for you response – handyx Jul 21 '14 at 01:19
  • sprintf("SELECT * FROM violation v left join violation_class vc on v.v_class=vc.id left join violation_type vt on v.v_type=vt.id left join declarant d on d.id=v.declearant left join importers imp on imp.id=v.importer WHERE (DATE_FORMAT(STR_TO_DATE('v.date_inpected','%%d/%%m/%%Y'), '%%Y-%%m-%%d') between %s and %s) and importer = %s ORDER BY date_inpected ASC", GetSQLValueString($colname_rs_violation, "int"), GetSQLValueString($colname2_rs_violation, "int"), GetSQLValueString($colname3_rs_violation, "int")); – handyx Jul 21 '14 at 01:29

1 Answers1

0

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');
James
  • 227
  • 1
  • 6