CODEWITHSUNDEEP
mysql
Mik
Mik

Reputation: 1703

How to fix wrong imported datetime in MYSQL

I did an import from a CSV file into the database and didnt recognize that the date was formated wrong so I ended up with dates like this: 

2027-05-14 00:00:00

instead of this

2014-05-27 00:00:00

I found this query but its not really what I am looking for. As im just able to maniuplate the Year but what I need actually is extracting the last 2 position of the year and set this as the day. The year is always 2014 and the month can stay as it is.

UPDATE table_name SET date_col=DATE_FORMAT(date_col,'2014-%m-%d %T');

Upvotes: 1

Views: 234

Answers (2)

CodeSlayer
CodeSlayer

Reputation: 1327

I think you cannot use this part of your code

 date_col=DATE_FORMAT(date_col,'2014-%m-%d %T');

if you want to get the value of date_col, select it first via subquery. Look at my example

 UPDATE table_name SET date_col=DATE_FORMAT((select date_col from table_name where _____),'2014-%m-%d %T');

Upvotes: 0

Tordek
Tordek

Reputation: 10882

UPDATE table_name SET date_col=DATE_FORMAT(date_col,'2014-%m-%y %T');

%y is the year in 2 digits.

A slightly more reliable way is

UPDATE table_name SET date_col=DATE_FORMAT(date_col,'20%d-%m-%y %T');

to put the day in the years place and vice-versa.

Upvotes: 2

Related Questions