Post reply

Date Type to store .0 as 0 or 0.0 and store 5 decimal positions

  • September 15, 2004 at 1:53 pm

    #162211

    I want a data type to store .0 as 0 or 0.0. I also want it to store 5 decimal positions (rounded). I have played around with the float, real, decimal and numeric data types. The float will store the .0 as 0.0 but when I store a number like 12345.123456789 it rounds it to 12345.123460000001. I want it to store it as 12345.12346. I have found a work-around to this by reading in the records from the SQL Table that stores the .0 and then in a Script I check to see if the value is equal 0 (which is the .0 values), if it is then I write 0.0 to my text file. I need to store 0 or 0.0 in my text file instead of .0.

    The Decimal (20, 5) and Numeric (20, 5) Data Types store the 12345.123456789 value correctly as 12345.12346 but they store a 0 value as .0. I do not want the .0, I want 0 or 0.0.

    The Real Data Type only stores 3 Decimal Positions. It stores the .0 as 0.0 but it stores 12345.123456789 as 12345.123.

    How may I get the data type to store .0 as 0 or 0.0 and also stor 5 decimal positions (rounded)?

    Thanks in advance.

     

  • September 15, 2004 at 3:08 pm

    #523241

    Kevin,

    This is (in my opinion) a data presentation issue, not a data storage issue.  If we can look at it that way, then there is an easy solution:  use the STR function to format the number when you select it to export to your text file.  That's what STR is for - determining the text format with which to display a number.  Well, at least if there are no commas required! 

    Here's a sample script:

    declare @mydec0 decimal (10,5)
declare @myflt0 float
declare @mydec1 decimal (10,5)
declare @myflt1 float
    set @mydec0 = 0
set @myflt0 = 0
set @mydec1 = 12345.123456789
set @myflt1 = 12345.123456789
    select str(@mydec0, 11, 5) as mystr0, str(@myflt0, 11, 5) as myflt0,
       str(@mydec1, 11, 5) as mystr1, str(@myflt1, 11, 5) as myflt1
    Output:
mystr0      myflt0      mystr1      myflt1      
----------- ----------- ----------- ----------- 
    0.00000     0.00000 12345.12346 12345.12346

    Hope this helps!

    Chris

     

  • September 15, 2004 at 3:09 pm

    #523243

    Oh, one more thing:  the length in the STR function is listed as 11, not 10, because this length has to include space for the decimal point as well as the 5 digits before and after the decimal point.

    -Chris

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply