always rounding down

Question

always rounding down

Dave Sundell

SSChasing Mays

Points: 653
More actions
August 10, 2005 at 7:47 am

#88433

I need to truncate down (always round down) to 2 decimal place. The field is declared as money. For example:
200.2345 should be 200.23
200.2355 should be 200.23
Any use of the convert function does rounding.
The purpose is to calculate a per pay amount from a month amount and we have to give the benefit to the person (always rounding down).
Any suggestions?
I have searched on this and one posting around 7/8/2004 didn't seem to work in this case.

Thanks

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Here's the only way I can think of...

Convert the value to a string using STR with all decimals intact, then just snip off the last two digits with substring...

declare @m as money

set @m = 200.2355

select substring(str(@m, 10, 4),1,8)

--------

200.23

(1 row(s) affected)

/Kenneth

Dave Sundell SSChasing Mays Points: 653 More actions · Answer 2

Dave Sundell

SSChasing Mays

Points: 653

August 10, 2005 at 8:04 am

#581037

Thanks, that worked.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Great

Perhaps you need to adjust the numbers so your max values will fit.

select substring(str(@m, 10, 4),1,8)

The 10 is max total length including all decimals and decimal points etc, the '8' should be 2 less than the number in '10' position.

/Kenneth

Phil Parkin SSC Guru Points: 247037 More actions · Answer 4

Or this

declare @m as money

set @m = 200.2365

select round(@m,2,1)

It does the rounding that you want, but leaves the 4 dps.

Dave Sundell SSChasing Mays Points: 653 More actions · Answer 5

This the round seems to be much simpler and also works.

Thanks to all

Lowell SSC Guru Points: 323518 More actions · Answer 6

yet another version:

declare @m as money

set @m = 200.2365

select floor((@m * 100)) / 100

results:

200.2300

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Sundell SSChasing Mays Points: 653 More actions · Answer 7

Gosh, I guess the phrase, "many ways to skin a cat" applies here. However, where did that phrase ever come from?

PhilPacha Hall of Fame Points: 3645 More actions · Answer 8

You usually don't want to use FLOOR, as this causes a conversion to float, which is an APPROXIMATE number. Stick with human-style math, like the ROUND with truncation mentioned in an earlier post.

Lowell SSC Guru Points: 323518 More actions · Answer 9

are you sure about the floor function returning an approximate number? the BOL says it returns the integer portion of the value, and returns it as the same data type.

I always interpreted this say it truncated at the decimal point and returned a value of the same datatype....so are you saying it could potentially return an approximation of say 87.345 as 87.00000000000000001?

i couldn't find an example of this being true so far....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!