data formatting for xfer from csv to mysql

[showman13]

Hi All,

Another roadblock on my way to fame and fortune... LOL

I am attempting to convert a csv file to a mysql table, and am using a converter that takes the csv and converts each line into an insert statement. that part works fine.

But the issue I run into is when I do the actual insert, the date field in the table shows all 00000 instead of the date that was inserted, which is formatted '9/30/2010'
AND the dollar fields are showing up as whole numbers as opposed to the dollars and cents that were entered.

I'm sure it is a simple fix, but just haven't found it yet.

I tested it with each date format available in mysql, and it didn't make a difference.

and the dollar issue I just noticed as I was typing this.

Here is one of the insert statements

INSERT INTO mem_trans (`date`,`time`,`name`,`type`,`status`,`currency`,`gross`,`fee`,`net`,`note`,`email`,`trans_id`,`subsc_number`,`receipt_id`,`zip`,`country`,`phone`) VALUES ('9/30/2010','23:38:19','john doe','Web Accept Payment Received','Completed','USD','75','-2.48','72.52','','[e-mail]','0E66a284625fA5890592L','','','','','');

and this is what the data looks like in the table
1
0
0000-00-00 00:00:00
23:38:19
john doe
Web Accept Payment Received
Completed
USD
75
-2
73
[e-mail]
0E66a284625fA5890592L

Any help that could be offered would be greatly appreciated.

Thanks
Douglas

[hielo]

Instead of ...VALUES('9/30/2010',...) use ...VALUES(STR_TO_DATE('9/30/2010','%m/%d/%Y'),....) MySQL will save the date in yyyy-mm-dd format. So when you retrieve the date you will need to use DATE_FORMAT to change it back to mm/dd/yyyy SELECT DATE_FORMAT(`date`,'%m/%d/%Y') as `date` FROM Table... As for your other problem, it sounds like you are using an integer data type for your field. Try using decimal instead.