Post reply

Numeric datatype result rounding off

  • June 29, 2011 at 12:01 am

    #253633

    Hi,

    The result of the following SQL rounds off to 8 decimal places whereas the scale is mentioned as 20. Can anyone suggest how can I avoid rounding off and obtain result till 20 decimal places.

    select isnull(convert(numeric(30,20),(convert(numeric(30,20),333) / convert(numeric(30,20),nullif(11,0)))),1)

    30.27272727000000000000

    In sybase the same sql returns the result as below

    30.27272727272727272727

    I would like the same result in SQL also.

    Any suggestions would be appreciated.

    Thanks in advance.

    Regards,

    Shakeel Ahmed Ansari

    DBA

  • June 29, 2011 at 1:10 am

    #1345677

    Which data type are you using in Sybase? Which version of Sybase?

    See if you're happier with one of the approximate data types in MS SQL Server:

    select isnull(convert(float,(convert(float,333) / convert(float,nullif(11,0)))),1)

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 29, 2011 at 3:26 am

    #1345741

    Actually, we have migrated from sybase 12.5.1 to SQL Server 2005. But because of different behaviour of numeric datatype in Sybase and SQL we are experiencing differences in calculations/reports.

    Sybase doesn't round off the decimal digits whereas SQL does and also the scale behaves differently in both Sybase and SQL. We are not able to find any workaround for this problem. Here we cannot use float also as suggested by you as it does not match the sybase result.

    Thanks

    Shakeel

  • June 29, 2011 at 3:49 am

    #1345753

    I don't think you'll get any better, in terms of the number of digits returned to the right of the decimal point, than FLOAT. I am interested to know why you need such precision?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 29, 2011 at 4:01 am

    #1345765

    The client is not accepting these differences hence we need to arrive to the same precision as Sybase.

    Thanks,

    Shakeel

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

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