how to truncate the data without rounding

[tanyasharma]

Hi
I have one numeric field in sql server table in which some row's values are like 123.23233443....E
My problem is that I want this field into a specific format to show into a textfile. required format is
########.#### Please suggest me how to get this format without any rounding.
No rounding should take place I just want data upto 4 decimal places.
please response as soon as possible
Thanks

 

[tlbroadbent]

The following will work. Perhaps, someone can suggest a simpler method.

Select Str(Floor(MyField*10000)/10000.,12,4)

Multiplying by 10000 provides 4 decimal places. Use a different factor for a differnt number of decimals. 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.

 

[bperry]

That's a nice solution.

I've been on conversion projects where we had to fill in any leading spaces with zeros. If that was needed here, it's a trivial change to add Replace():

Select REPLACE(Str(Floo(MyField*10000)/10000.,12,4),' ','0')

from YourTable