Undesired results in output 1

[jalbao]

i have a field that is a 'float' datatype. the values in the field do no have more than 2 digits to the right of the decimal. eg. 145.23.

the strange thing is, is when i query and output the values, all of a sudden i get crazy lengths to the right of the decimal. eg. 145.23000000000000001.

how to i stop this from happening?

i'm running sql server 2k on win2k sp2.
the data type is 'float' - length is 8 and i do not have a default value defined

 

[tlbroadbent]

Floating point data is approximate; not all values in the data type range can be precisely represented.

If you always want to show 2 decimals, convert the data type to decimal or numeric. If you can't change the database, then convert the result of the query using CAST or CONVERT functions.

Select cast(floatcol as Decimal(8,2)) As DecValue
From table1 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jalbao]

ahhh.. thank you sir

 

[jalbao]

so what datatype do i use if i want to store and output a value that is exactly as was entered by the application user?

 

[tlbroadbent]

Decimal or numeric. They are equivalent. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jalbao]

hmm.. i've tried decimal and i use prcesion = 18 and scale = 4. the output forces a four decimal value.

ex. if the user enters 456 for a value, the output sql server spits out is 456.0000.

i'm would like to avoid any extra scripting to have the values displayed as was entered by the user: ie. i would like the output to display 456 not 456.0000.

btw: i've searched the books online to help me with this to no avail.

 

[tlbroadbent]

Hmm... How will the users view the data? Which tool(s) or program(s) will they use? Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jalbao]

this application is done over the

http://www so

they will be using thier browser - all scripting is done on the server side via coldfusion.

 

[jalbao]

also, i forgot to mention - i used query analyzer to query my table to make sure that my problem at hand was not a coldfusion deal. the results were the same - sql server forces a four decimal output.

one thing that strikes me as odd, is that when i manually look at each row of data in my table, all of the values are EXACTLY the way that the user entered the data (btw: when i say user, i'm referring to me - still deving). it is not until i output the data that sql server appends the .0000 to the original value.

thanks a bunch for all you input terry

 

[tlbroadbent]

Every tool handles data presentation formatting differently. Enterprise Manager and Query Analyzer may display values in the same column quite differently. In my opinion, it is best to allow SQL to return native data and format the data presentation in Cold Fusion as it builds the web page. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jalbao]

i'll take your advice.. thanks again

 

[rajeevnandanmishra]

Hi albao,
Just for thought!
You said "i've tried decimal and i use prcesion = 18 and scale = 4."
Whatever value you are putting in scale, it is the no. of decimal places. Like here you had defined scale=4, which means you want the data to be stored as 4 places of decimals. But from your Question, i am assuming that you want data to be stored only upto two decimal places, then please put the scale value = 2. And if you don't want any decimal place then you can make the scale value=0.

So whatever number you had entered in scale, Sql server will return the values upto those many decimal places.

 

[jalbao]

rajeevnandanmishra - i understand what you are saying, but my problem is this: i use scale = 4 because four decimal precision is all i need. But i want the output of the value to be displayed exactly as the user originally entered it. For example: if the user enters the value, 452.123, i want sql server to output 452.123 (not 452.1230) - or if the user enters the value 452.1 i want sql server to output 452.1 (not 452.1000). i don't know how many decimals the user will enter - in fact, the user may not enter any decimals, but i do know that he will never enter more than four. so the problem is, how to i get sql server to a allow decimal values and output ONLY what the user initially entered.

 

[rajeevnandanmishra]

Hi albao,
Ok! now i understand your requirement.
Please let us know that if one user had entered 452.1000 and the other one 452.10, what you are going to show to user? and how are you going to identify that wheather user had putted 452.1000 or 452.1?

May be a varchar data type will be perfect for this. In which the numeric validation can be done in the front end and in the back-end the data will be stored as it is entered by the user.

 

[jalbao]

thx rajee - i'll look into the varchar datatype and see if it will work for me...

i will be executing a bunch of math functions using the values that we have been talking about.. are there any warnings that pop in your head with using varchars in mathematical situations?

 

[rajeevnandanmishra]

Hi albao,
I don't think there will be any problem, in converting varchar to numeric or vice-versa if data in varchar column is perfectly numeric.
Though whenever you will be reading this, you have to convert it in numeric and at the point of writing convert back the values to character.