Hi guys,

I need to convert a date field(dd/mm/yyyy) into mysql date column(yyyy-mm-dd)

table
========

CREATE TABLE fujitsu_spare_import (
date_temp date NOT NULL default '0000-00-00',
time time NOT NULL default '00:00:00',
scan_type text NOT NULL,
fujitsu_order_number text NOT NULL,
technician_or_shippiment text NOT NULL,
import_status tinyint(1) default '0'
) TYPE=InnoDB;



query
========

LOAD DATA INFILE "P:\\test.csv"
INTO TABLE fujitsu_spare_import
FIELDS TERMINATED BY ','
(str_to_date(date_temp, '%d/%m/%Y'),time,scan_type,fujitsu_order_number);

Example csv file
======

21/06/1921,15:20:45,R,9000804991Test,,0



Database - mysql 5.0
===============

The above query is not working, can anyone pls help me out.

Cheers
Anand

add another column to the table, VARCHAR(10), load the date string into this extra column, then run an UPDATE statement with STR_TO_DATE, and finally ALTER the table and drop the extra column

Thanks ,very muh for the help...

I will use ur suggestion,

update fujitsu_spare_import
set `date_spares`= str_to_date(date_temp,'%d/%m/%Y')
where scan_type like '%R%'

created a new column date_spares and its fine now.

But should there be a way to update while ur using the load query?

Anway thanks once again