change date format with alter table

I have script that daily download "test.csv" from client ftp, in that csv file I have one date field in the format ('mm/dd/yyyy') and table has field xd format ('yyyy-mm-dd').

I upload csv file using load data command.

Please suggest me how can i handle this situation.

If there is any way i can change date format with alter table command or load data have any 开发者_如何学Gooption to convert to table format.

Any help will make my work easy.

Thank you.

In PHP you can write a script to convert your date to a time and then convert the time into a new date.

Something like this:

$time = strtotime( $originalDate );

$newDate = date( 'yyyy-mm-dd', $time );

See the php documentation for exact syntax

Date: http://php.net/manual/en/function.date.php Time: http://php.net/manual/en/function.strtotime.php

You can load the values into a user variable before assigning to the date field with STR_TO_DATE.

LOAD DATA INFILE 'test.csv'
INTO TABLE test
(@date_value)
SET date_field = STR_TO_DATE(@date_value, '%d/%m/%Y')

A downside to this approach is that you must specify the columns you are importing to.