how to you make this 44.8730084 into this: 44.8%

Question

how to you make this 44.8730084 into this: 44.8%

99posts

SSChasing Mays

Points: 627
More actions
December 28, 2009 at 11:33 am

#218988

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31164 More actions · Answer 1

Go to BOL and look up numeric data types . . .

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Ray K SSC-Dedicated Points: 31164 More actions · Answer 2

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 SSC-Insane Points: 23606 More actions · Answer 3

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.comLearnSQLWithBru

Twitter -- BruMedishetty

99posts SSChasing Mays Points: 627 More actions · Answer 4

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 Valued Member Points: 71 More actions · Answer 5

select substring(cast(44.8730084 as varchar),1,4)+'%'

Ray K SSC-Dedicated Points: 31164 More actions · Answer 6

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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 SSChasing Mays Points: 627 More actions · Answer 8

is ok... is a very small db.

let me see what i can do with these examples.

thanks to all!

🙂

Ray K SSC-Dedicated Points: 31164 More actions · Answer 9

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/

99posts SSChasing Mays Points: 627 More actions · Answer 10

99posts

SSChasing Mays

Points: 627

December 28, 2009 at 2:57 pm

#1096838

prima! outstaken!