tsql - sql server datetime浮动错误

Question

我试图通过将 datetime::float 值与 1/3 进行比较来计算工作班次，如下所示：

select t, 
    cast( ( cast( cast(t as datetime) as float) - floor(cast( cast(t as datetime)  as float ) ) ) * 3.0 as decimal(38,30) ) x,
    floor( 
        ( cast( cast(t as datetime) as float) - floor(cast( cast(t as datetime)  as float ) ) ) * 3.0 
        ) x1
from ( values ('2019-01-01 00:00:00.0') , ('2019-01-01 07:59:59.0') , 
              ('2019-01-01 08:00:00.0') , ('2019-01-01 15:59:59.0') , 
              ('2019-01-01 16:00:00.0') , ('2019-01-01 23:59:59.0') ) test_sample(t)

但结果令人困惑：

2019-01-01 00:00:00.0   0E-30   0.0
2019-01-01 07:59:59.0   0.999965277776937010000000000000    0.0
2019-01-01 08:00:00.0   1.000000000007276000000000000000    1.0
2019-01-01 15:59:59.0   1.999965277784212900000000000000    1.0
2019-01-01 16:00:00.0   1.999999999992724100000000000000    1.0 <----- inconsistency, expected 2.0
2019-01-01 23:59:59.0   2.999965277769661100000000000000    2.0

为什么 16:00:00 与其他值不同？我该怎么做才能正确解决此错误（不将日期与很多case whens 进行比较）？

标签： tsqlsql-server-2012rounding-error