Link to home
Start Free TrialLog in
Avatar of Bird757
Bird757Flag for South Africa

asked on

CAST in SQL 2012 within formula resulting in invalid / unexpected results

I need to group variables in a calculation and have been using the CAST feature to apply what would typically be brackets in Excel.

However, the results are making no sense. I hope someone can assist with why I am getting the following results?


select @@version
-- I have tested this in SQL 2008 R2 and the results are the same.
/*
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
      Dec 28 2012 20:23:12
      Copyright (c) Microsoft Corporation
      Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
*/
GO
Declare @fLat1 NUMERIC(11, 7)

Select @fLat1 = CAST(33 AS [float])
Select @fLat1 = @fLat1 + CAST(57/60 AS [float])
Select @fLat1
/* Return is 33.0000000 */
GO

Declare @fLat1 NUMERIC(11, 7)

Select @fLat1 = CAST(57/60 AS [float])
Select @fLat1 = @fLat1 + CAST(33 AS [float])
Select @fLat1
/* Return is 33.0000000 */
GO

Declare @fLat1 NUMERIC(11, 7)

Select @fLat1 = 0.95
Select @fLat1 = @fLat1 + CAST(33 AS [float])
Select @fLat1
/* Return is 33.9500000 */

The above is completely unexpected.
The reason I am using the CAST is to be certain that the various parts of the formula are grouped, and calculated in the expected way. I was completely surprised to find that the impact is not at all what I expected.

I have tried using a [float] instead of a numeric - same result.
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I didn't see any difference at all,



----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
      Jun 17 2011 00:54:03
      Copyright (c) Microsoft Corporation
      Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)



---------------------------------------
33.0000000


---------------------------------------
33.0000000


---------------------------------------
33.9500000






----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
      Oct 19 2012 13:38:57
      Copyright (c) Microsoft Corporation
      Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)



---------------------------------------
33.0000000


---------------------------------------
33.0000000


---------------------------------------
33.9500000
Avatar of Bird757

ASKER

Thank you. Yes, that is the problem and I was not seeing it.