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