multiply by 1/4

Hi,
What's wrong with this:
DECLARE @.a as float
DECLARE @.b as float
SET @.a = (0.25) * 100
SET @.b = (1/4) * 100
print @.a
print @.b
Returns:
25
0
Why is the following returning 0 and not 25?
SET @.b = (1/4) * 100
Something similar is coded somewhere in one of our apps and is causing
incorrect reporting.
Thanks!gracie wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
Do:
SET @.b = (1/4.0) * 100 ;
Otherwise you get an integer division, which equals 0.
Better still, specify a datatype for numeric literals:
SET @.b = (CAST(1 AS FLOAT)/CAST(4 AS FLOAT)) * 100 ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Perfect. Thanks.
Since they both return the correct result, why is specifying the datatype
for numeric literals better?
"gracie" wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!|||It gives you control of the input datatype so you can determine over which datatypes the operation
will occur.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:D037A0AB-7A95-4285-9471-3695FAB55CFC@.microsoft.com...
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
> "gracie" wrote:
>> Hi,
>> What's wrong with this:
>> DECLARE @.a as float
>> DECLARE @.b as float
>> SET @.a = (0.25) * 100
>> SET @.b = (1/4) * 100
>> print @.a
>> print @.b
>> Returns:
>> 25
>> 0
>> Why is the following returning 0 and not 25?
>> SET @.b = (1/4) * 100
>> Something similar is coded somewhere in one of our apps and is causing
>> incorrect reporting.
>> Thanks!|||gracie wrote:
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
Otherwise, how can you guarantee which numeric datatype, scale and
precision the server will pick? Not specifying the datatype may work
the way you expect it today, but the next version of SQL Server may
change things. In fact, the rules for implict casting of datatypes have
changed several times in different releases of SQL Server.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--