[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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?
0
willa666
Asked:
willa666
  • 2
1 Solution
 
nemws1Database 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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now