.00 in a Decimal type.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

.00 in a Decimal type.

Post 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. :P
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: .00 in a Decimal type.

Post by josh »

float
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: .00 in a Decimal type.

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: .00 in a Decimal type.

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: .00 in a Decimal type.

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: .00 in a Decimal type.

Post by josh »

Decimal fields store the .00 even if you pass a whole #, I believe was the anomaly he was complaining of
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: .00 in a Decimal type.

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: .00 in a Decimal type.

Post by Benjamin »

Well... don't you have to be careful with floats?

24.00 can turn into 23.9999999999999945454
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: .00 in a Decimal type.

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: .00 in a Decimal type.

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: .00 in a Decimal type.

Post 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.
User avatar
freepaidsoftware
Forum Newbie
Posts: 2
Joined: Thu Feb 26, 2009 12:15 am
Location: Bangalore

Re: .00 in a Decimal type.

Post by freepaidsoftware »

astions.. :D
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: .00 in a Decimal type.

Post 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? :oops: 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
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: .00 in a Decimal type.

Post 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:
  1. 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.
  2. Decimal values must be limited to two places. So 12.345 will be stored as 12.34.
  3. 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: .00 in a Decimal type.

Post 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
Post Reply