Cast and Convert

Question

Post reply

Cast and Convert

Susan-322874

SSCrazy

Points: 2300
More actions
July 20, 2007 at 11:26 am

#125118

The ReplyAVMLotsize field is an 'INT' datatype, while the sa_lotsize is a decimal(14,0)
I need to update the AVMLotSize with the SA_lotsize.
However, I can run the updates below for a 100 records, but then I get the following error message
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
I tried both cast and convert...
update
details
set
AVMLotSize = CONVERT(int, sa_LotSize)
from
details
where
sa_property_id = sa_property_id
and
avmlotsize is null

or
update
details
set
AVMLotSize = cast(sa_LotSize as int)
from
details
where
sa_property_id = sa_property_id
and
avmlotsize is null

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

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

what happens when you

select cast(sa_LotSize as int)

from details

where

sa_property_id = sa_property_id /* why is this here?*/

and

avmlotsize is null

Susan-322874 SSCrazy Points: 2300 More actions · Answer 2

Susan-322874

SSCrazy

Points: 2300

July 20, 2007 at 11:39 am

#721234

Figured it out..

Thanks

Susan

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

glad you figured it out, but it would be beneficial to others that read this post to know what the issue was, as there be others with a similar issue.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

most likely the data doesn't convert because decimal(14,0) is bigger than an int.

begin

declare @dec decimal(14,0)

set @dec = 99999999999999

select cast(@dec as int)

end

when it hits the row that has a bigger number it fails.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Susan,

Please post what you figured out... thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)