How can i transform '12 June 2013' as a DATE TIME

Hiya All

I have a csv file that i will be importing into a MYSQL table once a day and one of the fields is a date filed but I recive it from my source in a "12 June 2013" format.

So what are my options, for importing this CSV via PHPmyAdmin and convert this to a proper useable DATETIME format?
LVL 1
willa666Asked:
Who is Participating?
 
nemws1Connect With a Mentor Database AdministratorCommented:
Import your data into a table, making your date field a VARCHAR(30).  Let's say you named it "import_date".

After importing:

ALTER TABLE your_table ADD fixed_date DATETIME;

UPDATE your_table
SET fixed_date = STR_TO_DATE(import_date, "%d %M %Y")
;

ALTER TABLE your_table DROP import_date;

Open in new window


Obviously, the STR_TO_DATE() is the fun part of all this.
0
 
willa666Author Commented:
Hiya nemws1

That worked great!

I am guessing that i should probably import this as a tmp table and then move this data into to the other data table once the transformation has been completed.

FYI i already added this to the end :)
ALTER TABLE orders_tbl change fixed_date sale_date datetime;

Ww
0
 
nemws1Database AdministratorCommented:
tmp table is a great idea!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.