By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table t(c float);
insert into t values
(0.46999999999999996),
(0.46999999999999997),
(0.46999999999999998),
(0.46999999999999999),
(0.47),
(0.00000000000000001),
(0.47000000000000002),
(0.47000000000000003),
(0.47000000000000004);
9 rows affected
select distinct c from t;
| c |
|---|
| 1E-17 |
| 0.47 |
| 0.47 |
select c, abs(c), round(abs(c),2) from t
where abs(c) <> round(abs(c),2);
| c | (No column name) | (No column name) |
|---|---|---|
| 1E-17 | 1E-17 | 0 |
| 0.47 | 0.47 | 0.47 |
| 0.47 | 0.47 | 0.47 |
| 0.47 | 0.47 | 0.47 |
select c, abs(c) AS ABS, round(abs(c),2) AS ROUND from t
where cast(abs(c) AS varchar) <> Cast(round(abs(c),2) AS varchar);
| c | ABS | ROUND |
|---|---|---|
| 1E-17 | 1E-17 | 0 |