Page 1 of 1

modify date format in mysql

Posted: Tue Mar 18, 2008 6:03 am
by bouncer
hi there.

is possible to modify the date format by reading this date from a csv file ? this because i'm importing info from a csv file into a mysql table but the date in the csv is in this format dd-mm-yyyy and when i import the date field in that table appear like this 00000000, any ideas ?

thanks in advance

Re: modify date format in mysql

Posted: Tue Mar 18, 2008 8:37 am
by Zoxive
Is the database field a timestamp?

Code: Select all

$DateFromCvs = '18-04-2008'; // dd-mm-yyyy
$Date = strtotime($DateFromCvs); //1208491200

Re: modify date format in mysql

Posted: Tue Mar 18, 2008 10:55 am
by onion2k
Something like...

Code: Select all

LOAD DATA INFILE 'mydata.csv' INTO TABLE `mytable`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(`field1`, `field2`, `field3`, @datefield) 
SET 
`field4` = STR_TO_DATE(@datefield, '%d/%m/%Y');
I don't have anything I can really test that with so it might not work as it is.

Re: modify date format in mysql

Posted: Wed Mar 19, 2008 6:27 am
by bouncer
first of all, thanks for your reply. :)

i manage to test the example that you gave me, but i continue to have this in the date fields '0000-00-00'

[sql] (`field1`, `field2`, `field3`, @datefield) SET `field4` = STR_TO_DATE(@datefield, '%d/%m/%Y'); [/sql]

one more thing, is it possible to UPDATE the entire row if the `filed1` already exists in the table ? that's because i'll have one csv file that will have the same data but this data may change, but the `filed1`, primary key, never change, and in few day's that field may not exist in the csv file but i want to keep it in the table.

i'm truncating the table every time that i import the file, so that i can add or update the records.
Zoxive wrote:Is the database field a timestamp?

Code: Select all

$DateFromCvs = '18-04-2008'; // dd-mm-yyyy
$Date = strtotime($DateFromCvs); //1208491200
no, i've defined it like Date.

regards