Arithmetic overflow error converting numeric to data type numeric

[dflanagan]

I am running this query:

SELECT * FROM fedexflat where salefrom <= 9.37 AND saleto >= 9.37


Against a simple table called fedexflat that has three columns (freight, saleto, salefrom). Each column is varchar size: 255, and I am getting this error both in my ASP page, and when I run it in query analyser:

Microsoft OLE DB Provider for SQL Server error '80040e57'

Arithmetic overflow error converting numeric to data type numeric.

/partsdisplay.asp, line 291


I have searched the net, and I am STUMPED!! Please help!!

Dave

 

[Chopstik]

You are trying to compare a varchar(255) column to a numeric value. This will not work. You will need to convert your variables into numeric values and then do the comparison. Something akin to this:

SELECT * FROM fedexflat where (CONVERT(salefrom,decimal)) <= 9.37 AND (CONVERT(saleto,decimal)) >= 9.37

You may need to tweak this, but it should give you the general idea.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[dflanagan]

That did not work...

I got:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'decimal'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'decimal'.


Could I convert the varchar columns to comething else?

 

[Sheco]

Try switching the order of the arguments to the CONVERT method.

Cut'n paste from SQL Server docs:

CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax
Using CAST:

CAST ( expression AS data_type ) 

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments
expression

Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions. 

data_type

Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types. 

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. 

style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

 

[Chopstik]

Yes, you can go into BOL (Books OnLine) in SQL Server (provided that is what you are using) and look up the CONVERT functionality.

You can try using this as a start:

SELECT * FROM fedexflat where (CONVERT(salefrom,decimal(5,2))) <= 9.37 AND (CONVERT(saleto,decimal(5,2))) >= 9.37

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[Chopstik]

Yeah, or you can use Sheco's suggestion which is obviously better than me who looked up the same thing and then STILL did it wrong...

Sheesh...

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[Sheco]

It happens to me all the time!

 

[dflanagan]

Got it!

had to move the decimal word over, but that was it! Thanks!

Dave