November 19, 2013 at 9:12 am
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 %]
November 19, 2013 at 9:17 am
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.
November 19, 2013 at 11:02 am
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?
November 19, 2013 at 11:07 am
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.
November 19, 2013 at 11:10 am
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)))
November 19, 2013 at 11:12 am
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.
November 19, 2013 at 11:13 am
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 %]
November 19, 2013 at 11:15 am
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.
November 19, 2013 at 3:05 pm
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