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


how to you make this 44.8730084 into this: 44.8%


how to you make this 44.8730084 into this: 44.8%

Author
Message
99posts
99posts
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
use a select statement; how do you make this

44.8730084

into this

44.8 %

actually doesn't even have to be a percent ( % ) could simply
be a truncate of sorts to appear like this 44.8

any help, or direction on this would be great.

many thanks
Ray K
Ray K
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2624 Visits: 4564
Go to BOL and look up numeric data types . . .

+--------------------------------------------------------------------------------------+
‌Check out my blog at https://pianorayk.wordpress.com/
Ray K
Ray K
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2624 Visits: 4564
99posts (12/28/2009)
use a select statement; how do you make this

44.8730084

into this

44.8 %

BTW, 44.873 would round to 44.9, not 44.8 (this is the math guy in me coming out here) . . .

+--------------------------------------------------------------------------------------+
‌Check out my blog at https://pianorayk.wordpress.com/
Bru Medishetty
Bru Medishetty
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 1950
Ray is right. You would be getting 44.9 rather than 44.8

Start with this.

select ROUND(44.8730084,1) -- gives 44.9000000 as output


Or try this way.

declare @val float;

set @val = 44.8730084

select ROUND(@val,1,1) -- gives 44.8 as output
select ROUND(@val,1,0) -- gives 44.9 as output




Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
99posts
99posts
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
thanks for the replies... the value
i gave was just a sample not the actual results coming out of the query.


and it wouldn't be just 'that' value; would be applied to any value returned from
the 'percent' column.

i'm not that great with tsql, but from the looks of it the examples apply to just
one value and not the range queried ( forgive me if i misunderstand )

any how; what about truncating the 52.2027837 to something like this
52.2

any ideas?

thanks in advance
ravi_m_s
ravi_m_s
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 24
select substring(cast(44.8730084 as varchar),1,4)+'%'
Ray K
Ray K
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2624 Visits: 4564
Did you read BOL like I suggested? (Not trying to dance around the answer -- just trying to encourage you to look it up and figure it out on your own before asking questions, which is highly encouraged in this forum.)

There are a number of data types that will do this for you. For example, try DECIMAL.

Try messing with the numbers (including the 4 and 1 in the decimal declaration) in this example in your SQL and see what it does for you . . .

declare @number decimal(4,1)

select @number = 10.2436
select @number



+--------------------------------------------------------------------------------------+
‌Check out my blog at https://pianorayk.wordpress.com/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47374 Visits: 44395
ravi_m_s (12/28/2009)
select substring(cast(44.8730084 as varchar),1,4)+'%'


Why would you want to convert to a varchar just to round a number off? String conversions are expensive operations.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


99posts
99posts
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
is ok... is a very small db.

let me see what i can do with these examples.

thanks to all!

Smile
Ray K
Ray K
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2624 Visits: 4564
If you're trying to convert from a table, try playing with this example:

declare @temp table(thisval float)

insert into @temp values (23.61)
insert into @temp values (1234.13456143)
insert into @temp values (100.00001)
insert into @temp values (12.34)
insert into @temp values (3.14159)
insert into @temp values (10.01)

select cast(thisval as decimal(6,2)) from @temp


Note: if I'm not mistaken (and someone correct me if I'm wrong), it's generally not advisable to put a function into a SELECT statement, since it can use processor time.

+--------------------------------------------------------------------------------------+
‌Check out my blog at https://pianorayk.wordpress.com/
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