Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conversion failed when converting the varchar value '98.97%' to data type int. Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 12:51 PM
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 %]
Post #1515633
Posted Tuesday, November 19, 2013 9:17 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 693, Visits: 5,943
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.
Post #1515637
Posted Tuesday, November 19, 2013 11:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 12:51 PM
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?
Post #1515685
Posted Tuesday, November 19, 2013 11:07 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 693, Visits: 5,943
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.
Post #1515686
Posted Tuesday, November 19, 2013 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 12:51 PM
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)))
Post #1515689
Posted Tuesday, November 19, 2013 11:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 693, Visits: 5,943
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.
Post #1515691
Posted Tuesday, November 19, 2013 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 12:51 PM
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 %]
Post #1515692
Posted Tuesday, November 19, 2013 11:15 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 693, Visits: 5,943
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.
Post #1515693
Posted Tuesday, November 19, 2013 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 12:51 PM
Points: 9, Visits: 8
Yup got it! I was really beating my head against the wall, thanks again.....
Post #1515805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse