Error Converting nvarchar to int?

Posted on 2006-05-01
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

    why dont you try Float instead of int

    Convert(Float, Field_Name)

    Author Comment

    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

    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

    Missed a ')' in my previous post

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

    Accepted Solution

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how the fundamental information of how to create a table.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now