CODEWITHSUNDEEP
mysqlsqldate
grasshopper
grasshopper

Reputation: 1401

Format date for mysql insert

Im using LOAD DATA INFILE to import a csv file, the files date format is 29/11/2010 and the database format is 2010-11-29, what can i use to format the date inside the query?

I've tried str_to_date:

SET date_start = STR_TO_DATE(@from_date,'%Y-%m-%d'),

but that only inserts 0000-00-00

Upvotes: 1

Views: 816

Answers (3)

Alma Do
Alma Do

Reputation: 37365

MySQL 4.x

LOAD DATA will try to insert your dates as they are. It isn't aware about format and in common case you can not apply some post-processing to your fields (well, different from some format which is allowed inside LOAD DATA syntax itself) - and you can not adjust your values via SET keyword like in MySQL 5.x

Instead you can do following steps:

  • Declare your table's column as VARCHAR. Let it name be record_date
  • Do your LOAD DATA query. It will load your dates into record_date column
  • Add new column to your table, let it be temp_date - with type DATE: ALTER TABLE t ADD temp_date DATE
  • Update your temp_date column: UPDATE t SET temp_date = STR_TO_DATE(record_date, '%d/%m/%Y')
  • Drop your VARCHAR date column: ALTER TABLE t DROP record_date
  • Finally, rename column with correct DATE type to original one: ALTER TABLE t CHANGE temp_date record_date DATE

As result, you'll have your dates loaded into your table as DATE date type. Replace record_date to the name which your original column has.

MySQL 5.x

You can use SET keyword and natively replace procedure, described above. So just do:

LOAD DATA INFILE 'file.csv'
INTO TABLE t
(@date)
SET record_date=STR_TO_DATE(@date, '%d/%m/%Y')

-sample above is for one column and you'll need to add others (if they exist). Date column name is also record_date - so change it to actual name too.

Upvotes: 1

Nagaraj S
Nagaraj S

Reputation: 13484

Use DATE_FORMAT() . It will Formats the date value according to the format string. Mysql

date_format(@from_date,'%Y-%m-%d')

Upvotes: 0

Gopal Joshi
Gopal Joshi

Reputation: 2358

Try something like

update tablename SET date_start = date_format(@from_date,'%Y-%m-%d')

Upvotes: 0

Related Questions