0
    if exists (select 1 from sys.tables where name = 'd_ratio') begin drop table d_ratio end
    if exists (select 1 from sys.tables where name = 'elrd') begin drop table elrd end

    CREATE TABLE [dbo].[d_ratio](
        [index] [bigint] NULL,
        [Class] [bigint] NULL,
        [Dratio] [float] NULL,
        [ELR-Act1] [float] NULL,
        [ELR-Act2] [float] NULL
    ) ON [PRIMARY]
    GO

    Insert into D_ratio
    select 0,2503,0.285,0.75,1.33

    CREATE TABLE [dbo].[ELRD](
        [RATE_EFFDT] [date] NOT NULL,
        [CLASS] [varchar](4) NOT NULL,
        [ELR] [decimal](5, 2) NULL,
        [D_RATIO] [decimal](5, 2) NULL,
        [EXPO_ACT] [char](2) NOT NULL
    ) ON [PRIMARY]
    GO

    truncate table elrd
    insert into elrd(Rate_effdt, class, elr, d_ratio, EXPO_ACT)
    select getdate(), right('0000' + cast(class as varchar(10)),4), round(cast([ELR-Act1] as decimal(10,3)),2),Dratio, 1
    from d_ratio

    select Dratio, * from d_ratio
    select D_ratio, * from elrd

It goes in as 0.285 and comes out as 0.28.

However if I cast it as a decimal with 3 points of precision like so:

    truncate table elrd
    insert into elrd(Rate_effdt, class, elr, d_ratio, EXPO_ACT)
    select getdate(), right('0000' + cast(class as varchar(10)),4), round(cast([ELR-Act1] as decimal(10,3)),2), cast(dratio as decimal(10,3)), 1
    from d_ratio

It goes in as 0.285 and comes out as 0.29 - it gets rounded properly.

Question What is SQL Server doing? Is it truncating floats and rounding decimals?

  • 1
    Have you considered just using decimal (which rounds as you expect) instead of float (which does not)? The behavior differences are documented in the standard, but as a peer offering advice I would simply recommend using the type that works and not using the one that doesn't. – Aaron Bertrand May 29 at 19:44
  • @AaronBertrand I can - I'm importing it from excel, so it is coming in as float. I suppose I could cast it on the initial import process. – James May 29 at 19:46
  • You're importing it from Excel into a table, right? That table can be defined with decimal columns, not float columns. – Aaron Bertrand May 29 at 19:47
2

Reading the SQL Server manual, it appears that the database is using an IEEE float under the hood.

As such, "weird things" are known to happen.

See this FAQ from SQLite (I suspect it applies here also):

(16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.