[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Error Converting nvarchar to int?

Posted on 2006-05-01
Medium Priority
Last Modified: 2008-01-09
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?

Question by:MARefresh
LVL 23

Expert Comment

by:Saqib Khan
ID: 16577708
why dont you try Float instead of int

Convert(Float, Field_Name)

Author Comment

ID: 16577729
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?
LVL 20

Expert Comment

ID: 16577785
You can use ROUND

Convert(int, Round(yourfieldname, 1)

Round returns a numeric expression, rounded to the specified length or precision
LVL 20

Expert Comment

ID: 16577796
Missed a ')' in my previous post

Convert(int, Round(yourfieldname, 1))
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16579073
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 '

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question