Bird757
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Yes, that is the problem and I was not seeing it.
--------------------------
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