How to truncate a calculated value to decimal places without rounding

Question

Post reply

How to truncate a calculated value to decimal places without rounding

Farooq-478117

SSC Veteran

Points: 294
More actions
September 11, 2008 at 11:45 am

#382624

Consider this:
declare @amount decimal(16,2)
declare @periods int
set @amount = 0.30
set @periods = 12
SELECT @amount/@periods
Result:
0.0250000000000
SELECT CONVERT(DECIMAL(16,2),@amount/@periods), CAST(@amount/@periods As DECIMAL(16,2))
Result:
0.030.03
I want to get a value of 0.02 on conversion, I want the truncate to 2 decimal places without the rounding, is it possible without converting the calculated result to string,then substring and then convert back to decimal, i don't want to take that route.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Farooq-478117 SSC Veteran Points: 294 More actions · Answer 1

I did found the solution:

CAST(ROUND(@Amount/@Period,2,1)AS DECIMAL(16,2))

works for me

Thankks Everyone.

Quatrei.X SSCrazy Points: 2697 More actions · Answer 2

Hi there,

i tried yours without using ROUND and I found something wierd and funny for me.:P

declare @amount decimal(16,2)

SET @amount=0.025555

SELECT FLOOR(0.025555*100)/100

-- 0.020000:w00t:

SELECT FLOOR(@amount*100)/100

-- 0.030000:hehe:

I think they should put this as a QOD

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Quatrei.X SSCrazy Points: 2697 More actions · Answer 3

Hi there agian,

My bad... I didn't notice that the decimal takes 2 decimal places which means 0.025555 was rounded of to 0.03 when I stored it to the variable, it will work same as cast.

Heres an easy answer to your problem

declare @amount decimal(16,3)

SET @amount=0.025555

SELECT FLOOR(@amount*100)/100

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

skottikalapoodi SSC Veteran Points: 268 More actions · Answer 4

hi actually i had similar doubt ...

i am trying to insert data from A table to another B table

A table has a value 26.153875 (decimal field (28,6)

when i inserted A table data into B table it is inserting 26.1539 as B table field design is decimal (28,4)

i want that B table should get 26.1538 only without round the value

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

DECLARE@Value DECIMAL(28, 6)

SET@Value = 26.153875

SELECT@Value AS OriginalValue,

ROUND(@Value, 4, 0) AS Rounding,

ROUND(@Value, 4, 1) AS Truncating

N 56°04'39.16"
E 12°55'05.25"

skottikalapoodi SSC Veteran Points: 268 More actions · Answer 6

Thanks... I am getting correct results with this.

However can you please confirm the following:

round(26.153875,4,1)

so here

first of all round will take upto 4 digits after decimal,

next as we mentioned 1 it is not rounding upto to next digit. Is it correct. Can you explain round function (truncating case) in simple words., as books online not clear about the truncating part..

it is saying "When a value other than 0 is specified, numeric_expression is truncated."

but it is not the case..

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Yes it is.

1 is "other value than 0".

N 56°04'39.16"
E 12°55'05.25"