overwflowed int column

Question

overwflowed int column

Krasavita

SSC-Insane

Points: 23138
More actions
May 25, 2011 at 10:07 am

#238877

Guys,
I have this code writen for field 54
CAST(REPLACE(F54, '.', '') AS int)
When I run this for data:1668541.0023
get error:overwflowed int column
How can I go around it.
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It occurs because when you remove the decimal point from the number, you end up with a number that's too big for the int data type. You can cast as bigint instead. It might help if you explain exactly what you're trying to do and why - we may be able to suggest a better way.

John

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Krasavita (5/25/2011)
When I run this for data:1668541.0023
get error:overwflowed int column

The following works?

DECLARE @F54 AS int

SET @F54 = 1668541.0023

SELECT CAST(REPLACE(@F54, '.', '') AS int)

-----------

1668541

(1 row(s) affected)

What is the Datatype of F54?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 3

Welsh Corgi (5/25/2011)
Krasavita (5/25/2011)
When I run this for data:1668541.0023
get error:overwflowed int column
The following works?
DECLARE @F54 AS int
SET @F54 = 1668541.0023
SELECT CAST(REPLACE(@F54, '.', '') AS int)
-----------
1668541
(1 row(s) affected)
What is the Datatype of F54?

this will definatley work because you are trating the F54 as int. the int will any way truncate the decimal values.

definatley F54 is not an integer, because if it is integer it doesn't need the above code.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

That is true but what is the datatype in the column of F54?

As was previously requested. please state what you are trying to do?

DECLARE @F54 Decimal (14,4)

SET @F54 = 1668541.0023

SELECT CONVERT(int,@F54)

SELECT CAST(REPLACE(@F54, '.', '') AS BigInt)

-----------

1668541

(1 row(s) affected)

--------------------

16685410023

(1 row(s) affected)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/