MySQL replace field entry with substring [duplicate]

Question

I currently have dates stored in my database (in string format) currently as mm/dd/yyyy.

I need to replace all entries as yyyy/mm/dd for sorting purposes.

To clarify, I am a PHP developer but my raw MySql isnt as strong. I was thinking of doing this by using substrings to re-arrange the date. (I tried in PHP first but it take way too long to process this much data, about a half million rows).

Am i going about this correctly? if so, how do I "loop through" the data, assigning my substr variables for the current iteration?

Note: I did try str_to_date here and it ended up nullifying half of the fields. Im not entirely sure why.

Your help is greatly appreciated.

stackoverflow.com/questions/9426993/mysql-change-date-format — Sturm
– Sturm, Commented Mar 8, 2013 at 17:15
And here we have the exact reason why the DATE and DATETIME data types exist. — Sammitch
– Sammitch, Commented Mar 8, 2013 at 17:22
To clarify, i am looking for an Update statement (sorry i should have said so) im also importing millions of rows from .CSV files with variable fields so importing as DATE was not an option here unfortunately. — Kirk Logan
– Kirk Logan, Commented Mar 8, 2013 at 17:31

echo_Me · Accepted Answer · 2013-03-08 17:29:34Z

1

this is an insert statment

  INSERT INTO yourtable (datefield) VALUES (str_to_date(date, '%Y/%m/%d'));

and this an update

 UPDATE Table SET date=STR_TO_DATE('date','%Y/%m/%d')

try this

   UPDATE Table SET date = DATE_FORMAT('date', %Y/%m/$d);

edited Mar 8, 2013 at 17:29

answered Mar 8, 2013 at 17:19

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

3 Comments

Kirk Logan Over a year ago

I tried your update statement exactly as written and all rows are now NULL for that field. Ideas?

echo_Me Over a year ago

look my updated answer

Kirk Logan Over a year ago

It is still nullifying the data. Very strange. But Matts answer below has worked perfectly so I can just go with that.

Matt Busche · Accepted Answer · 2013-03-08 17:20:10Z

0

I believe this will work if your column is in a consistent mm/dd/yyyy format

UPDATE mytable
SET date = CONCAT(right(date,4), '/', left(date,5))

answered Mar 8, 2013 at 17:20

Matt Busche

14.4k5 gold badges38 silver badges63 bronze badges

Collectives™ on Stack Overflow

MySQL replace field entry with substring [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related