0

I have a column called dispatchdate

It is currently storing dates as a string (varchar) format. It stores it as e.g. 16/07/2013

How can I convert all my data in into date format without editing them all one by one?

Improve this question
1
  • 1
    Use the same solution as the linked answer, however you'll need to use the date format of %d/%m/%Y instead of %d-%m-%Y. And no need for the PHP code -- use a plain old MySQL client! Commented Jul 23, 2013 at 23:57

1 Answer 1

Reset to default
1

A possible solution

UPDATE Table1
   SET dispatchdate = DATE_FORMAT(STR_TO_DATE(dispatchdate, '%d/%m/%Y'), '%Y-%m-%d');

ALTER TABLE Table1 
      CHANGE dispatchdate dispatchdate date;

Here is SQLFiddle demo

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks peterm - that seems to work on my test database - although I notice that it now displays the date as 2013-06-12 - is there anyway to get this as 12-06-2013?
see above comment - is there a way to change it from 2013-06-12 to 12-06-2013
Use DATE_FORMAT(dispatchdate, '%d-%m-%Y') for that