CODEWITHSUNDEEP
sql-server-2008
Oliver
Oliver

Reputation: 11607

Update Decimal valued fields in SQL Server

I want to do the following to my database:

UPDATE Addresses

SET Latitude = 1000, Longitude = 1000
WHERE Latitude IS NULL AND Longitude IS NULL

1000 is not a valid latitude or longitude. Latitude and Longitude are new columns in my database, and I don't want any of the fields to be null. I handle invalid latitude and longitude values in my application, but having them null creates difficulties.

When I try to run this query, I get the following error:

Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.

The datatypes of the Latitude and Longitude column are both Decimal(18,15). I assume that I have to write these decimal entries in a particular way, but I can't work out what it is.

Could someone enlighten me?

Upvotes: 2

Views: 15906

Answers (1)

JNK
JNK

Reputation: 65177

You are overflowing your specified parameters for the datatype.

DECIMAL(18,15) means you have 18 total places, and 15 of those are right of the decimal. This leaves 3 places left of the decimal, and 1000 is a four digit number.

You need to either change the datatype to something like DECIMAL(18,14) or use a value like 999.999999999999999.

Upvotes: 7

Related Questions