Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
joey6401c
joey6401c
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 8
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 @compliant int
declare @notcompliant int
declare @total int
declare @percent int

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 %]
roryp 96873
roryp 96873
SSC Eights!
SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)

Group: General Forum Members
Points: 857 Visits: 6598
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.
joey6401c
joey6401c
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 8
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?
roryp 96873
roryp 96873
SSC Eights!
SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)

Group: General Forum Members
Points: 857 Visits: 6598
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.
joey6401c
joey6401c
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 8
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)))
roryp 96873
roryp 96873
SSC Eights!
SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)

Group: General Forum Members
Points: 857 Visits: 6598
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.
joey6401c
joey6401c
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 8
THANK YOU!!!!!!!!!!

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

declare @compliant int
declare @notcompliant int
declare @total int
declare @percent decimal(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 %]
roryp 96873
roryp 96873
SSC Eights!
SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)

Group: General Forum Members
Points: 857 Visits: 6598
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.
joey6401c
joey6401c
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 8
Yup got it! I was really beating my head against the wall, thanks again.....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search