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.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Bird757

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Aneesh

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
ASKER
Bird757

Thank you. Yes, that is the problem and I was not seeing it.
Your help has saved me hundreds of hours of internet surfing.
fblack61