Why does sql server have a hard time rounding floats? 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?
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?