Convert float data to string - have a problem

[Miriam]

Hi everybody,

I need to convert float data type to string, but I don't want round the data. For example:

MyData = .3999999 or =.5

I want to have
MyResult = .399 or .500 (with no round!)

I tried STR, CONVERT, CAST function and combination of this function and LEFT, LEN finction and etc. Cannot find the solution. Sometimes it's giving me a good looking result when I used MyData = .3999999 but it's not working for MyData = .5


Any helt will be appreciate,
Miriam

 

[tlbroadbent]

This will work though it will add a leading zero but you can remove that if needed.

Select
MyResult = Cast(Cast(Cast(MyData*1000 As Int)/1000.
As Decimal(10,3)) As Char) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Miriam]

Terry,

Thanks a lot, it's works.

But it's works perfectly ONLY if I have
MyData=0.4199999999999999 (16 digits after .)

If MyData =0.41999999999999999 (one more zero - my actual data) it's round the result. I think my task just impossible.

Miriam

 

[tlbroadbent]

Yes, your task seems impossible because SQL Server doesn't store the number with that much accuracy. Try this.

Declare @v float
Set @v=0.49999999999999999 --17 digits after decimal
Select V=@v

The result will be 0.5.

Set @v=0.4999999999999999 --16 digits after decimal
Select V=@v

The result will be 0.49999999999999989

Note that neither result is the exact value. That is because float is an approximate data type and approximate numbers cannot be represented precisely or exactly. Therefore, it is not the conversion that is the problem but the storage of the data. If you want exactness you must use the Decimal or Numeric data type. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Miriam]

Terry,

Thanks a lot for advice and explanation. I will change the data type.


Miriam