troubleshooting Question

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

Avatar of Bird757
Bird757Flag for South Africa asked on
Microsoft SQL Server 2008
3 Comments1 Solution376 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros