In case any astute mathematicians were paying attention to this thread -
the previous solution I offered would obviously fail on Negative values
- which may or may not be a requirement.
Either way, I've amended it to work for all values, but I think Keith's
method might be better than this amended method, if not in speed, then
certainly in complexity terms:
--
================================================================================================
WITH SRC(Val) AS (
VALUES
(0),(1.0000001),(1.12345678),(1.99999999),(1.888),(9.87654321),(1.555555555),(1.4999999),(1.49494999)
UNION ALL
SELECT -Val FROM SRC LIMIT 18
)
SELECT Val,
round(CAST(Val AS INTEGER)+ (((Val * 1000) % 1000)/1000),3) AS
Tr_JeanM,
CAST(Val*1000 AS INT) / 1000.0 AS Tr_Keith,
round(Val-(ifnull(Val/abs(Val),0)*0.000499999),3) AS Tr_Ryan
FROM SRC
;
-- Val | Tr_JeanM | Tr_Keith | Tr_Ryan
-- ---------------- | ---------- | ---------- | ----------
-- 0 | 0.0 | 0.0 | 0.0
-- 1.0000001 | 1.0 | 1.0 | 1.0
-- 1.12345678 | 1.123 | 1.123 | 1.123
-- 1.99999999 | 1.999 | 1.999 | 1.999
-- 1.888 | 1.888 | 1.888 | 1.888
-- 9.87654321 | 9.876 | 9.876 | 9.876
-- 1.555555555 | 1.555 | 1.555 | 1.555
-- 1.4999999 | 1.499 | 1.499 | 1.499
-- 1.49494999 | 1.494 | 1.494 | 1.494
-- 0 | 0.0 | 0.0 | 0.0
-- -1.0000001 | -1.0 | -1.0 | -1.0
-- -1.12345678 | -1.123 | -1.123 | -1.123
-- -1.99999999 | -1.999 | -1.999 | -1.999
-- -1.888 | -1.888 | -1.888 | -1.888
-- -9.87654321 | -9.876 | -9.876 | -9.876
-- -1.555555555 | -1.555 | -1.555 | -1.555
-- -1.4999999 | -1.499 | -1.499 | -1.499
-- -1.49494999 | -1.494 | -1.494 | -1.494
WITH SRC(Val) AS (
VALUES
(0),(1.0000001),(1.12345678),(1.99999999),(1.888),(9.87654321),(1.555555555),(1.4999999),(1.49494999)
UNION ALL
SELECT -Val FROM SRC LIMIT 18
)
SELECT Val,
round(CAST(Val AS INTEGER)+ (((Val * 1000000) %
1000000)/1000000),6) AS Tr_JeanM,
CAST(Val*1000000 AS INT) / 1000000.0 AS Tr_Keith,
round(Val-(ifnull(Val/abs(Val),0)*0.000000499999),6) AS Tr_Ryan
FROM SRC
;
-- Val | Tr_JeanM | Tr_Keith | Tr_Ryan
-- ---------------- | ------------- | ------------- | -------------
-- 0 | 0.0 | 0.0 | 0.0
-- 1.0000001 | 1.0 | 1.0 | 1.0
-- 1.12345678 | 1.123456 | 1.123456 | 1.123456
-- 1.99999999 | 1.999999 | 1.999999 | 1.999999
-- 1.888 | 1.888 | 1.888 | 1.888
-- 9.87654321 | 9.876543 | 9.876543 | 9.876543
-- 1.555555555 | 1.555555 | 1.555555 | 1.555555
-- 1.4999999 | 1.499999 | 1.499999 | 1.499999
-- 1.49494999 | 1.494949 | 1.494949 | 1.494949
-- 0 | 0.0 | 0.0 | 0.0
-- -1.0000001 | -1.0 | -1.0 | -1.0
-- -1.12345678 | -1.123456 | -1.123456 | -1.123456
-- -1.99999999 | -1.999999 | -1.999999 | -1.999999
-- -1.888 | -1.888 | -1.888 | -1.888
-- -9.87654321 | -9.876543 | -9.876543 | -9.876543
-- -1.555555555 | -1.555555 | -1.555555 | -1.555555
-- -1.4999999 | -1.499999 | -1.499999 | -1.499999
-- -1.49494999 | -1.494949 | -1.494949 | -1.494949
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
