Solved

# convert real to in

Posted on 2009-07-03
278 Views
Hi Experts, I'm having a problem I don't understand. I need to cast a real to a integer and I test on the maximum possible int value. When I do casting I get the "Arithmetic overflow error for type int, value = 2147483648.000000"

Check the code

tnx leon
``````DECLARE @myFloatToInt AS FLOAT

SET @myFloatToInt = 2147483647

DECLARE @myFloatToIntToBig AS FLOAT

SET @myFloatToIntToBig = 6666666666

DECLARE @myRealToInt AS REAL

SET @myRealToInt = 2147483647

DECLARE @myRealToIntToBig AS REAL

SET @myRealToIntToBig = 6666666666

SELECT  CASE WHEN @myFloatToInt > 2147483647 THEN 2147483647

ELSE @myFloatToInt

END AS myFloatToInt

, CASE WHEN @myFloatToIntToBig > 2147483647 THEN 2147483646

ELSE @myFloatToIntToBig

END AS myFloatToBig

, CASE WHEN @myRealToInt > 2147483647 THEN 2147483647

--             ELSE @myRealToInt

ELSE CAST(@myRealToInt AS INT)

END AS myRealInt

, CASE WHEN @myRealToIntToBig > 2147483647 THEN 2147483646

ELSE @myRealToIntToBig

END AS myRealToBig
``````
0
Question by:sojoca
• 4
• 3
• 2

LVL 23

Accepted Solution

Racim BOUDJAKDJI earned 350 total points
ID: 24771709
From memory and if I am not mistaken the value 2147483648.000000 is above the defined threshold for int.  Please cast as bigint instead.

HTH
0

LVL 14

Assisted Solution

Jagdish Devaku earned 150 total points
ID: 24771726
hi check the below code... i have changed INT to BIGINT in the above code in CAST.
please let me know if there is any issue.
bye.

``````DECLARE @myFloatToInt AS FLOAT

SET @myFloatToInt = 2147483647

DECLARE @myFloatToIntToBig AS FLOAT

SET @myFloatToIntToBig = 6666666666

DECLARE @myRealToInt AS REAL

SET @myRealToInt = 2147483647

DECLARE @myRealToIntToBig AS REAL

SET @myRealToIntToBig = 6666666666

SELECT  CASE WHEN @myFloatToInt > 2147483647 THEN 2147483647

ELSE @myFloatToInt

END AS myFloatToInt

, CASE WHEN @myFloatToIntToBig > 2147483647 THEN 2147483646

ELSE @myFloatToIntToBig

END AS myFloatToBig

, CASE WHEN @myRealToInt > 2147483647 THEN 2147483647

--             ELSE @myRealToInt

ELSE CAST(@myRealToInt AS BIGINT)

END AS myRealInt

, CASE WHEN @myRealToIntToBig > 2147483647 THEN 2147483646

ELSE @myRealToIntToBig

END AS myRealToBig
``````
0

LVL 23

Expert Comment

ID: 24771736
I'd suggest making all compares in the same data type (preferably the one with the largest capacity) to avoid data type precedence cast issues.
0

LVL 23

Expert Comment

ID: 24771749
Please read the following to understand what data type precedence is.

http://msdn.microsoft.com/en-us/library/ms190309.aspx
HTH
0

Author Comment

ID: 24771848
let me re-phrase: I'm writing some adaptor in SISS and the real-field and the integer-field are given; I can't change the type. Because the real-field can contain a bigger numeric value then the integer-field I need to check the max value as in:
SELECT  CASE WHEN @myFloatToInt > 2147483647 THEN 2147483647
ELSE @myFloatToInt
END AS myFloatToInt
But when the value is bigger than 2147483647 than iit gets rounded to 2147483648.000000 (which is bigger than the max int-value 2147483647)
0

LVL 23

Expert Comment

ID: 24771997
You need to do explicit casting...

SELECT  CASE WHEN @myFloatToInt > cast(2147483647 as real) THEN 2147483647
ELSE @myFloatToInt
END AS myFloatToInt
0

Author Comment

ID: 24772083
hi Racimo, still doesn't solve my problem. Check

--             ELSE @myRealToInt  --  is OK
ELSE CAST(@myRealToInt AS INT)  -- goes wrong

in the code snippet

``````DECLARE @myFloatToInt AS FLOAT

SET @myFloatToInt = 2147483647

DECLARE @myFloatToIntToBig AS FLOAT

SET @myFloatToIntToBig = 6666666666

DECLARE @myRealToInt AS REAL

SET @myRealToInt = 2147483647

DECLARE @myRealToIntToBig AS REAL

SET @myRealToIntToBig = 6666666666

SELECT  CASE WHEN @myFloatToInt > 2147483647 THEN 2147483647

ELSE @myFloatToInt

END AS myFloatToInt

, CASE WHEN @myFloatToIntToBig > 2147483647 THEN 2147483646

ELSE @myFloatToIntToBig

END AS myFloatToBig

, CASE WHEN @myRealToInt > CAST(2147483647 AS REAL) THEN 2147483647

--             ELSE @myRealToInt  --  is OK

ELSE CAST(@myRealToInt AS INT)  -- goes wrong

END AS myRealInt

, CASE WHEN @myRealToIntToBig > 2147483647 THEN 2147483646

ELSE CAST(@myRealToIntToBig AS INT)

END AS myRealToBig
``````
0

LVL 14

Expert Comment

ID: 24772103
hi,
cast to bigint will solve the issue. but the value might crossed the range of bigint. I suggest you to keep it to float or cast it to nvarchar or so...
bye
0

Author Closing Comment

ID: 31599529
I didn't "see" the solution rightaway, hence my re-phrase. Extra words of the experts would have helped me. I was to busy to see I guess
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

#### 762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!