0

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','','johndoe@jdoe.com','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
johndoe@jdoe.com
0E66a284625fA5890592L


Any help that could be offered would be greatly appreciated.

Thanks
Douglas

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by hielo
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.