Geek Answers Handbook

Updating date format in mysql

I am working on a table in which there are two date formats stored in the field, some of which are in mm / dd / yy, and new entries are in the format yyyy / mm / dd, as it should be.

I want to run such a request

UPDATE table
   SET date_field = DATE_FORMAT(date_field, '%Y/%m/%d') 
 WHERE date_field = DATE_FORMAT(date_field, '%m/%d/%y')

But it just doesn’t work. One of the results I got was that it just took% m data and turned it into% Y and really messed up the data.

Any thoughts?

+5
source share
3 answers

You want to use the STR_TO_DATE function, not DATE_FORMAT. Also, I assume that you want to update the incorrect dates, so I think you could do this:

UPDATE your_table
SET date_field = DATE(STR_TO_DATE(date_field, '%m/%d/%Y'))
WHERE DATE(STR_TO_DATE(date_field, '%m/%d/%Y')) <> '0000-00-00';

P.S. , . , DATE

+13

STR_TO_DATE, , DATE_FORMAT, .

, WHERE . , LIKE.

UPDATE your_table SET date_field =
    DATE_FORMAT(STR_TO_DATE(date_field, '%m/%d/%y'), '%Y/%m/%d') 
WHERE date_field LIKE '__/__/____'
+4

? , :

  • . , , , , ( ), MySQL , DATE, ; /. ( ) ( ).

  • , ( , ?) - y/m/d m/d/y, . ( 2010 - , 08/10/09 ).

  • Also, what idea is to store only 2 digits of a date? This is just such a shortsighted penny, which led to the fact that time and money were in 2000. (avoid if you saved the date, you know, in a date format).

+1
source

More articles:


All Articles