Arithmetic overflow error converting varchar to decimal

1
votes

Why does this work:

Declare @latitude varchar = '34.343';
Declare @decLatitude Decimal(9,6);
Set @decLatitude = CAST(@latitude AS Decimal(9,6));

But this doesn't?

Declare @longitude varchar = '-92.6424';
Declare @decLongitude Decimal(9,6);
Set @decLongitude = CAST(@longitude AS Decimal(9,6));

It returns the error "Arithmetic overflow error converting varchar to data type numeric".

This is on an Azure SQL database.

1
sqlsql-serverazure-sql-database
Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use - marc_s
Yeah, I was overly tired and wasn't thinking straight. - Joshua

1 Answers

1
votes

Are you sure you are getting what you wanted in your first query ? 

Declare @latitude varchar = '34.343';
Declare @decLatitude Decimal(9,6);
Set @decLatitude = CAST(@latitude AS Decimal(9,6));

Did you examine what is the value you stored in both of the variables ?

select  @latitude, @decLatitude

If you don't specify a size for the varchar, it is assumed 1

Declare @latitude varchar = '34.343';

so basically you are assigning 3 to @latitude and converting 3 to decimal is fine

In you second query, you are you are assigning a '-' to the @latitude So converting '-' to decimal will cause an error !

So declaring a varchar with size will work in your second query

Declare @longitude varchar(10) = '-92.6424';