April 8, 2009 at 8:48 am
Hi
I was working on some SQL when I came across this unexpected result - unexpected to me that is.
If I use NUMERIC(38,10) as the variables types in the SQL below:
DECLARE @a NUMERIC(38,10)
DECLARE @b NUMERIC(38,10)
SET @a = 2
SET @b = 3
SELECT @b / @a A, FLOOR(@b / @a) B, @b / @a - FLOOR(@b / @a) C
the results are
A B C
1.500000 1 1
I would have expected C to be 0.5 because it should be A - B.
If I change the NUMERIC(38,10) to FLOAT then I get what I expect.
DECLARE @a FLOAT
DECLARE @b FLOAT
SET @a = 2
SET @b = 3
SELECT @b / @a A, FLOOR(@b / @a) B, @b / @a - FLOOR(@b / @a) C
A B C
1.5 1 1.5
BOL says FLOOR returns the same type as the numeric expression.
If I CAST the FLOOR to NUMERIC(38,10) as below then I get what I expect.
DECLARE @a NUMERIC(38,10)
DECLARE @b NUMERIC(38,10)
SET @a = 2
SET @b = 3
SELECT @b / @a A, FLOOR(@b / @a) B, @b / @a - CAST(FLOOR(@b / @a) AS NUMERIC(38,10)) C
A B C
1.500000 1 1.500000
Why??
Thanks!
Derren
April 9, 2009 at 6:46 am
Book OnLine states that the FLOOR function returns the same datatype as the input but this statement is misleading because, for a NUMERIC datatype, the returned datatype is a NUMERIC with the same scale but with a precision of zero and not the precision of the input. I also found this incomplete explanation misleading and would also have expected the precision to be the same as the input.
You can determine the actual datatypes with the following SQL ( variables with a prefix of "N" indicates a numeric and a prefix of "F" indicates float). In the output of the column definitions, examine the columns "PRECISION" and "SCALE".
IF object_id('tempdb..#t') is not null drop table #t
DECLARE @N_A NUMERIC(38,10)
,@N_B NUMERIC(38,10)
, @F_A FLOAT
,@F_B FLOAT
SET @N_A = 2
SET @N_B = 3
SET @F_A = 2
SET @F_B = 3
SELECT @N_A AS [@N_A]
,@N_B AS [@N_B]
, @F_AAS [@F_A]
,@F_B AS [@F_B]
,@N_B / @N_A AS [@N_B / @N_A]
,@F_B / @F_A AS [@F_B / @F_A]
,FLOOR(@N_B / @N_A) AS [FLOOR(@N_B / @N_A)]
,FLOOR(@F_B / @F_A) AS [FLOOR(@F_B / @F_A)]
, @N_B / @N_A - FLOOR(@N_B / @N_A) AS [@N_B / @N_A - FLOOR(@N_B / @N_A)]
, @F_B / @F_A - FLOOR(@F_B / @F_A) AS [@F_B / @F_A - FLOOR(@F_B / @F_A)]
into #t
select name
,system_type_id
,CASE system_type_id
when 108 then 'Numeric'
when 62 then 'Float'
else 'Other'
endAS DataType
, precision
, scale
from tempdb.sys.columns where object_id = object_id('tempdb..#t')
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply