Here’s a simple query that searches all non round numbers from a table. So the following are non-round numbers 20.00, 24.0 and the following are round numbers 20.20, 24.42 and so on.
Here’s the query:
Here’s the same query for you to try out:
DECLARE @TT TABLE (
INSERT INTO @TT VALUES (1, 23.44);
INSERT INTO @TT VALUES (2, 21.00);
INSERT INTO @TT VALUES (3, 20.00);
INSERT INTO @TT VALUES (4, 53.30);
INSERT INTO @TT VALUES (5, 11.00);
SELECT * FROM @TT
ROUND(salary, 10) - FLOOR(ROUND(salary, 10)) = 0
Note: Alternatively, you can also write the same query using WHERE (salary – floor(salary)) = 0, or WHERE salary
!= FLOOR(salary), but I use the query shown above (learnt this tip from Erland Sommarskog) which can also handle values like 20.00000000000097. If you are using the Money datatype, then the alternatives I suggested will work without an issue, since it is a fixed point type.