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.
Bird757Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Select @fLat1 = @fLat1 + CAST(57/60 AS [float])
One of the more super-riffic features of SQL Server is that when it divides two integers, the whole number will be an integer, and if you cast the result as a float it'll still be an integer.

SELECT CAST(57/60 AS [float])

To get around this, cast the denominator, not the whole result

SELECT 57/CAST(60 as float)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Bird757Author Commented:
Thank you. Yes, that is the problem and I was not seeing it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.