Erland Sommarskog:
> Anton Sheplev:
>
> > Can you please tell me why this query:
> >
> > SELECT POWER(0.5, 2), POWER(0.50, 2)
> > Returns 0.3 0.25
> > Instead of 0.25 0.25
>
> All say after me: "SQL Server is designed for maximum
> confusion!".
Indeed, and after your epic article about error han-
dling.
> I know how it feels, I've run into exactly this thing
> myself. And I thought, what!? Then I read the manu-
> al ->
I was misled by the remark phrase in the documentation
that the first artument "is an expression of type float
or of a type that can be implicitly converted to float."
So I thought that since
CAST( 0.5 AS FLOAT) = CAST( 0.50 AS FLOAT )
the should be no difference between 0.5 and 0.50.
> -> and learnt this:
>
> The retun type of power() is the type of the first ar-
> gument. The 0.3 has the type decimal(1, 1), and so the
> return type is numeric(1,1) and you get 0.3. Intu-
> itive? Maybe not. But this is what the manual says.
Thank you, Erland. That explains it.
> By the way, this is how you can tell the type of a nu-
> meric literal:
> DECLARE @s sql_variant = 0.3
> SELECT sql_variant_property(@s, 'Basetype'),
> sql_variant_property(@s, 'Precision'),
> sql_variant_property(@s, 'Scale')
Yes, quite useful in debugging implicit and/or unintu-
itive type conversions.
> To avoid such surpises, say something like:
> SELECT cast(power(cast @val as float) as decimal(10,3))
That is what I did, albeit a bit plainer:
DECLARE @C_TWO FLOAT = 2 -- float constatnt two!
--- ...
POWER( @C_TWO, @lambda )
I think your outer `CAST' is superfluous because it can-
not increase actual precision.
--
() ascii ribbon campaign - against html e-mail
/\
http://preview.tinyurl.com/qcy6mjc [archived]