trim and round

Question

Post reply

trim and round

harita

SSCrazy

Points: 2197
More actions
September 21, 2010 at 11:53 am

#240667

How to round up a number and trim it to two digits, exe 1.434567 to 1.4

Viewing 15 posts - 1 through 14 (of 14 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Round up? If you round 1.434567 up, it'll be 1.5, not 1.4.

Can you clarify your requirement?

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

harita SSCrazy Points: 2197 More actions · Answer 2

i have a row of data with numbres like 1.43467,..,... and i want a sql code that can make it 1.4

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

What do you want to happen to numbers like this:

1.45

1.48

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

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 4

Congratulations on 20k, Gail. 🙂

saimddr:

The question is whether or not you want to round the data or truncate the data.

Rounding the data can increase or decrease its value.

Truncating the data just chops off everything after the specified position.

You can get either result with the ROUND() function.

declare @data float

set @data = 1.4632

select round(@data,1,0) as rounded, round(@data,1,1) as truncated

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

harita SSCrazy Points: 2197 More actions · Answer 5

no matter how big is the number i want first 2 digits

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 6

That would be truncation.

Will you ever have numbers like 14.567 or 143.953?

If so, do you want the answer to be 14?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Cory E. SSCertifiable Points: 7076 More actions · Answer 7

saimddr (9/21/2010)
no matter how big is the number i want first 2 digits

So,

123 is 12

123.987 is 123.9

0.91 is 0.9

.123 is .12?

Or, are you really after something like, get rid of everything past the first decimal place?

-- Cory

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 8

123.987 is 123.9

????

Cory, explain please.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

until we have some clarification and examples...here's an idea

suggest looking up "ROUND" in BOL

SELECT CAST(Round (1.434567, 1,1) AS DECIMAL (18, 1))

SELECT CAST(Round (1.4499999999, 1) AS DECIMAL (18, 1))

SELECT CAST(Round (1.450000000, 1) AS DECIMAL (18, 1))

SELECT CAST(Round (14.47, 1,1) AS DECIMAL (18, 1))

SELECT CAST(Round (1456666.4, 1,1) AS DECIMAL (18, 1))

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Cory E. SSCertifiable Points: 7076 More actions · Answer 10

The Dixie Flatline (9/21/2010)
123.987 is 123.9
????
Cory, explain please.

I was adding more examples to try and get the true requirement. 123.987, is that supposed to be 123.9? Make sense?

-- Cory

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 11

ROUND: http://msdn.microsoft.com/en-us/library/ms175003%28SQL.90%29.aspx

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 12

You spin me round round, baby, right round....

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 13

The Dixie Flatline (9/21/2010)
You spin me round round, baby, right round....

😀 Lol,Bob

harita SSCrazy Points: 2197 More actions · Answer 14

Thanks to all for the help.......

declare @x decimal(10, 4)

set @x = 3.4567

select @x, cast(@x as decimal(10, 1))