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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy