Post reply

Decimal datatype?

  • September 7, 2006 at 8:36 am

    #92378

    Greetings.  We're using ETL to transfer data from a flat file into a SQL Server 2005 table.  One of the fields contains a decimal, but the number of digits on the right side of the decimal vary.  (0.1275, 0.18, 0.07375, etc.)  I've tried using the float datatype, but that turns 0.1275 into 0.127499997615814 and so on.  When I use the decimal datatype, is there any way to set the scale to a value that will allow for different numbers of digits to the right of the decimal point?  I could probably use scale 6, but then 0.1275 turns into 0.127500.  (Not so bad if I have to do this.  I have to format in SSRS anyway.)  Is there a way to set the scale on the decimal datatype that will transfer the numbers from the flat file so the numbers will be the same in the SQL Server table (0.1275 to 0.1275, 0.18 to 0.18, 0.07375 to 0.07375, etc?) 

    Thanks.

    Tracey

  • September 7, 2006 at 8:46 am

    #659118

    Where are you presenting that data to anyone??  This is where you format it.  Sql server is supposed to be used mainly as a data repository... but that's changing now .

  • September 7, 2006 at 9:08 am

    #659127

    This data will be used in a SQL Server Reporting Services report.  The value will be calculated to a display a percentage in the report, so the trailing zeros are fine.  I just thought there might be a way to do this. 

    Thanks.

    Tracey

  • September 7, 2006 at 9:14 am

    #659129

    Then once you prensent the data you will format it... also much easier to do client side than sql server.

  • September 10, 2006 at 3:23 am

    #659518

    Did you try the ROUND function?

  • September 13, 2006 at 5:11 pm

    #660273

    Your trouble is the way floating point numbers are represented in memory, which leads to rounding errors. An eqivelent to this is in decimal we cannot write 1/3 exactly as it's 0.333333... What you want to do is and store them in the server at that, and as already said format them in the report for display.

    Sql Server doesn't have a variable numeric type which would work the way you suggested for the decmial, only fixed decimal types. Thus decide upon the max precsion and scale you need for data accuracy and store them like that as the db is all about storing the data correctly. The reports/client should be the one worrying about the presentation of that information.

Viewing 6 posts - 1 through 5 (of 5 total)

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