Conversion failed when converting the varchar value '98.97%' to data type int.

  • Hi guys,

    I need your help. I am receiving the following error from the set @percent line

    "Conversion failed when converting the varchar value '98.97%' to data type int."

    declare @compliantint

    declare @notcompliantint

    declare @totalint

    declare @percentint

    set @compliant = (select [# of patches] from vpatchcompliancecount where Status = 'compliant')

    set @notcompliant = (select [# of patches] from vpatchcompliancecount where Status = 'not compliant')

    set @total = (select SUM([# of Patches]) from vpatchcompliancecount)

    set @percent = CONVERT(varchar,convert(DECIMAL (4,2),100.0 *

    (select [# of Patches] from vpatchcompliancecount where Status = 'compliant')/(select SUM([# of Patches]) from vpatchcompliancecount))) + '%'

    select @compliant [Installed Patches], @notcompliant [Pending], @total [Total], @percent [Compliance %]

  • Well, you are trying to put a decimal(4,2) that you have appended a % to into an int column. That won't work for a couple of reasons. First an int can't have any decimal places. Second you added a non-numeric character in there, so it is a varchar that can't be converted to an int anyways. Do you really need the % in there? If not, make @percent a decimal(4,2), or (5,2) if it can be 100% and get rid of the converts when you are setting the value.

  • Hey,

    Thanks for the reply. All I am trying to do is return a compliance percentage of @compliant/@total and I want to return a percentage with 2 decimal places if possible so 98.97 let's say

    I don't need the % in there I just want to return the percent with two decimal places or more

    Do you know a query i can use for that?

  • joey6401c (11/19/2013)


    Hey,

    Thanks for the reply. All I am trying to do is return a compliance percentage of @compliant/@total and I want to return a percentage with 2 decimal places if possible so 98.97 let's say

    I don't need the % in there I just want to return the percent with two decimal places or more

    Do you know a query i can use for that?

    Just change this line:

    declare @percent int

    to

    declare @percent decimal(4,2)

    or

    declare @percent decimal(5,2)

    if you think you may get 100.00 back at some point. (4,2) will error on you if it returns 100 or larger.

  • Ok so here is the new code, it works but the problem is that the output for Compliance % is 98 and I want two decimal places after 98

    set @percent = (CONVERT(DECIMAL (4,2),100.0 *

    (select [# of Patches] from vpatchcompliancecount where Status = 'compliant')/(select SUM([# of Patches]) from vpatchcompliancecount)))

  • When you declare @percent at the top, did you change it from @percent int to @percent decimal(4,2)?

    If not it will implicitly convert it back to an int, which is why you lose your decimals.

  • THANK YOU!!!!!!!!!!

    I ended up using 6,4 but here is the final query and thanks again!

    declare @compliantint

    declare @notcompliantint

    declare @totalint

    declare @percentdecimal(6,4)

    set @compliant = (select [# of patches] from vpatchcompliancecount where Status = 'compliant')

    set @notcompliant = (select [# of patches] from vpatchcompliancecount where Status = 'not compliant')

    set @total = (select SUM([# of Patches]) from vpatchcompliancecount)

    set @percent = (CONVERT(DECIMAL (6,4),100.0 *

    (select [# of Patches] from vpatchcompliancecount where Status = 'compliant')/(select SUM([# of Patches]) from vpatchcompliancecount)))

    select @compliant [Installed Patches], @notcompliant [Pending], @total [Total], @percent [Compliance %]

  • Glad it worked. Just remember, if you ever get a percent back of 100, you'll have to make sure the first number (x) in your decimal(x,y) is at least 3 larger than the second (y), or else you'll get an arithmetic overflow error.

  • Yup got it! I was really beating my head against the wall, thanks again.....

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply