Page 1 of 2
.00 in a Decimal type.
Posted: Sun Feb 22, 2009 5:37 pm
by JellyFish
I have a price field in one of my tables in my database. The price field's type is decimal(12,2). I'd like to have the ability to insert decimal data into this field with decimal fractions (.00) but not require it. So for example, if I insert something into this field without a decimal fraction or .00 as the fraction then I'd like to omit it.
The reason I want this is because when someone chooses a price without a cent value I wont want to have to display it. I know I could just check if there's a decimal value when fetching rows through php, but I'd like a solution where I could avoid handling this after the fact.
So is there a way to ignore the decimal fraction of a number if if it's either .00 or .0 or . or not even there.
Thanks for reading. All help is appreciated on this.
PS: I hope I made myself understandable.

Re: .00 in a Decimal type.
Posted: Sun Feb 22, 2009 5:56 pm
by josh
float
Re: .00 in a Decimal type.
Posted: Tue Feb 24, 2009 12:26 pm
by Bill H
I'm not sure if you are talking about input into the database or display of output.
If the former, as far as I know if you input "23" or "23.0" or "23.00" into a decimal field you will get the same result.
For output you can use sprintf() function to get uniformity.
Re: .00 in a Decimal type.
Posted: Tue Feb 24, 2009 7:22 pm
by josh
Making it a float field will allow him to insert 23 and get 23, right now my understanding is hes trying to insert 23 and he's getting 23.00 back because the field is not a float.
Re: .00 in a Decimal type.
Posted: Wed Feb 25, 2009 5:03 pm
by Bill H
You may be right. I'm not clear on his question. I interpreted it to mean that he wanted his client to be able to input $23.00 as just 23 if they wanted instead of having to input the full 23.00 and get valid results when they omitted the zeros.
Re: .00 in a Decimal type.
Posted: Wed Feb 25, 2009 6:49 pm
by josh
Decimal fields store the .00 even if you pass a whole #, I believe was the anomaly he was complaining of
Re: .00 in a Decimal type.
Posted: Wed Feb 25, 2009 9:23 pm
by Bill H
I got that josh. You were responding to what you thought his question was. I was responding to what I thought his question was. We thought different things; read a poorly worded question different ways. Maybe to do with English not being his first language. On rereading, you may indeed have gotten his intent correctly.
Re: .00 in a Decimal type.
Posted: Wed Feb 25, 2009 10:43 pm
by Benjamin
Well... don't you have to be careful with floats?
24.00 can turn into 23.9999999999999945454
Re: .00 in a Decimal type.
Posted: Wed Feb 25, 2009 10:52 pm
by josh
I thought the question was pretty clear, but that doesn't matter. 24.00 won't turn into 23.99934734 unless you start doing certain types of math with floating point functions, I believe you had to do something like a summation of .1, I think direct assignments of whole #s guarantee you get a precise answer back.
Re: .00 in a Decimal type.
Posted: Thu Feb 26, 2009 12:35 am
by Bill H
The quesion was crystal clear. I was just utterly stupid in my interpretation of it. You can reply now and have the last word.
Re: .00 in a Decimal type.
Posted: Thu Feb 26, 2009 12:51 am
by Benjamin
Bill H wrote:The quesion was crystal clear. I was just utterly stupid in my interpretation of it. You can reply now and have the last word.
Oh Bill, don't be so hard on yourself. Every answer helps, everyone can view the question from a different angle and every answer helps on the big screen.
Re: .00 in a Decimal type.
Posted: Thu Feb 26, 2009 12:54 am
by freepaidsoftware
astions..

Re: .00 in a Decimal type.
Posted: Thu Feb 26, 2009 1:47 am
by josh
Man I'm not trying to be rude, you're not stupid obviously. I always feel like people make me feel like I'm argumentative, I guess I'm a jerk?

Sorry to anyone I bump heads with I only have the best intentions
"every answer helps" these are true words, I was just trying to defend the OP since he may have been offended, I was just reassuring him that his question did make sense, if anything to me... Not trying to "prove" anything, I was just trying to keep things unbiased which backfired on me I guess hah
Re: .00 in a Decimal type.
Posted: Sat Feb 28, 2009 1:08 am
by JellyFish
Sorry for leaving this post for so long, I've been really busy this week.
I'm trying to get the data within the database to contain both whole integers and decimals with a limit to two decimal places (so that's integers: 23 and decimals with two places: 23.50).
Now when I changed the price field to a FLOAT it solved the problem except for one thing. It allow more then two decimal places, so 12.333 was valid. When I set the price field FLOAT(12, 2) it had the same effect ad DECIMAL(12,2), which is decimals all the time even with a .00 decimal value.
So what I need is a data type that will meet this criteria:
- Values stored in the column do not require a decimal value when no decimal is written. So for example, If a number like 11 or 11.00 is inserted into the column, 11.00 will not be what's stored, rather just 11.
- Decimal values must be limited to two places. So 12.345 will be stored as 12.34.
- If possible, decimal values should ALWAYS contain two places. So 11.1 will be stored as 11.10. In other words, when ever there is a decimal value in the number there should always be two places displayed in the field.
I'd really like to meet these issues with MySQL, or at least as much as possible. Also, this field is called price, I will be storing monetary values in it. So does FLOAT has any inconsistencies that may not be suitable for dealing with money?
Thank you so much for all the input already. Obviously I wasn't in a clear state of mind in my OP. I hope I've written more clearly this time.
Re: .00 in a Decimal type.
Posted: Sat Feb 28, 2009 5:52 am
by josh
You'd have to implement this with business logic. I supposed it would do a round to length 2, and then store the field. Float will be fine unless you need to add .00000001 and .00091919 and numbers like that. I would do the rounding in application code but thats just me