trailing zeroes not going into database

Question

So here is my issue, We are trying to put pricing into our database but for some reason it converts 10.00 to 10 OR 10.50 to 10.5 it keeps it just chops off the trailing zeroes....

I checked the SQL statement and it says price=10.00 .... so Im really confused as to why/where it is stripping the .00

Ive checking all the way through where we enter the number to the final step at the INSERT or UPDATE and it keeps the trailing zeroes all the until the UPDATE or INSERT and then when you check the table ...the zeroes are dropped

I just checked the column in the DB is a double .... hmmmm thanks mitch, I will work on trying to format it — Ray, Aug 11 '11 at 15:39
NOTE: Be very wary using a double datatype to store monetry values.... — Mitch Wheat, Aug 11 '11 at 15:39
Indeed Mitch. I would go further and advise *not* to do so. Use a `DECIMAL` type instead. — Hammerite, Aug 11 '11 at 15:44
@Hammerite: Yes, exactly. I don't know why I was obtuse! DON"T use double to store monetry values. — Mitch Wheat, Aug 11 '11 at 15:51

andrew cooke · Accepted Answer · 2011-08-11T15:49:12.023

are you using a decimal(N,2) type, or similar, for storing the data? if not, then this is normal behaviour. see http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

please, don't follow the advice in another answer and leave this to your presentation layer. do the job properly and store the exact decimal value. that way you avoid rounding issues.

ps there's also the separate issue of how the value is managed in whatever language you are using. hopefully it uses a special type that includes appropriate formatting. if not, then you may want to worry about how you handle the value in your code (one possibility is to multiple by 100 and use an integer number of cents, but then you need to take care that maths rounds correctly - this is complicated, but it's complicated for a reason; if you just stuff everything in doubles it will likely be simple, but have some strange bug for certain values...)

+1. you are correct. I typed my first comment to the OP's question above before I realised it was a 'price' column. — Mitch Wheat, Aug 11 '11 at 15:54
I am not ... I am using double, I will try to change to decimal — Ray, Aug 11 '11 at 16:00

score 0 · Answer 2 · answered Aug 11 '11 at 15:38

0

10 is the same as 10.00 is the same as 10.0

if you want to display with those trailing digits, or as it's more commonly known 2 decimal places, then you will need to write your code to do that. Since you've not said what language you're using I can't really help you with that part.

answered Aug 11 '11 at 15:38

Antony Scott

21,690
12
62
94

The data is retrieved through an object using php – Ray Aug 11 '11 at 15:55
take a look here - http://stackoverflow.com/questions/324358/zero-pad-digits-in-string – Antony Scott Aug 11 '11 at 16:07

score 0 · Answer 3 · answered Aug 11 '11 at 15:41

0

It doesn't matter how it looks in the database. What matters is how you present data in your app. 10.00 is the same value as 10, so in the data table it will be saved as 10.

You have to format number before output.

answered Aug 11 '11 at 15:41

vaidas

514
3
9

trailing zeroes not going into database

3 Answers3

Linked