Error Converting nvarchar to int?

I am trying to convert the varchar value 0.33 to an int for a Reporting Services report and get the following error message:

Reporting Services Error
--------------------------------------------------------------------------------
 
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Query execution failed for data set 'MyDB'. (rsErrorExecutingCommand) Get Online Help
Syntax error converting the varchar value '0.33' to a column of data type int.

I am using temporary tables and stored procedures to populate and retrieve the data.  Does anyone know why this conversion/cast is not allowed?

Thanks.
MARefreshAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
int is entire numbers, while 0.33 is a decimal number, hence:

SET @wk_WhereClauseHigh          = ' CAST( Priority as decimal(10,3) ) BETWEEN 0 AND 11 '
SET @wk_WhereClauseMedium      = ' CAST( Priority as decimal(10,3) ) BETWEEN 12 AND 23 '
SET @wk_WhereClauseLow           = ' CAST( Priority as decimal(10,3) )  BETWEEN 24 AND 36 '

if that still does not work, your SQL Server might be complaining about the "." as decimal character and prefer the "," :

SET @wk_WhereClauseHigh          = ' CAST( REPLACE(Priority , '.' , ',') as decimal(10,3) ) BETWEEN 0 AND 11 '
SET @wk_WhereClauseMedium      = ' CAST( REPLACE(Priority , '.' , ',') as decimal(10,3) ) BETWEEN 12 AND 23 '
SET @wk_WhereClauseLow           = ' CAST( REPLACE(Priority , '.' , ',') as decimal(10,3) )  BETWEEN 24 AND 36 '
0
 
Saqib KhanSenior DeveloperCommented:
why dont you try Float instead of int

Convert(Float, Field_Name)
0
 
MARefreshAuthor Commented:
I will try that.  Thanks.  Basically I am converting the varchar (0.33) to int to compare to 3 different ranges of values:

SET @wk_WhereClauseHigh            = ' CONVERT(int, Priority) BETWEEN 0 AND 11 '
SET @wk_WhereClauseMedium       = ' CONVERT(int, Priority)  BETWEEN 12 AND 23 '
SET @wk_WhereClauseLow             = ' CONVERT(int, Priority)  BETWEEN 24 AND 36 '

If it falls between 0 and 11 it is considered 'High'... and so forth... that is why int made sense.

Any thoughts?
0
 
SireesCommented:
You can use ROUND

Convert(int, Round(yourfieldname, 1)

Round returns a numeric expression, rounded to the specified length or precision
0
 
SireesCommented:
Missed a ')' in my previous post

Convert(int, Round(yourfieldname, 1))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.