How to trim the decimal places?

Question

Post reply

How to trim the decimal places?

Nuts

SSCrazy

Points: 2547
More actions
September 18, 2008 at 4:38 pm

#192929

Hi Guys
I have a column with the figures which have 4 decimal points.
I want to trim them to two decimal points.
How do I do it??
Can it be done fron the reporting services as well??
Thanks

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

You could use the CONVERT function for example:

DECLARE @Num4 AS DECIMAL(10,4)

DECLARE @Num2 AS DECIMAL(10,2)

SET @Num4 = 1234.9876

SET @Num2 = CONVERT(DECIMAL(10,2),@Num4)

SELECT @Num2

@Num2 will then equal 1234.99 -- note the rounding which has taken place.

of course this works as well

DECLARE @Num4 AS DECIMAL(10,4)

DECLARE @Num2 AS DECIMAL(10,2)

SET @Num4 = 1234.9876

SET @Num2 = @Num4

SELECT @Num2

@Num2 will then equal 1234.99 -- note the rounding

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Much simpler than all that... check out the ROUND function or the STR function (which also right justifies if you need that for a report or file). 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

blessybaby SSC Journeyman Points: 99 More actions · Answer 3

Pls try this

DECLARE @Num4 AS DECIMAL(10,4)

SET @Num4 = 1234.9876

SELECT STR(@Num4,Len(@Num4),2)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

Nuts (9/18/2008)
Hi Guys
I have a column with the figures which have 4 decimal points.
I want to trim them to two decimal points.
How do I do it??
Can it be done fron the reporting services as well??
Thanks

Post some sample data with expected result so that we dont need to guess what you really want

Madhivanan

Failing to plan is Planning to fail

manjunath5581 SSC Veteran Points: 226 More actions · Answer 5

Hi,

I want to trim the value of decimal point to 2.

For example

1245.658797

123597.599945

798754.589785

787561.124657

I want this to be displayed as

1245.65

123597.59

798754.58

787561.12

If i try to use decimal(15,2) it will round off the value.

If i convert to type money then also the values get round off

The value type is real in the database

Please help...

arun.sas SSChampion Points: 11831 More actions · Answer 6

hi,

try this,

create table #temp

(

amount decimal(15,6)

)

insert into #temp

select 1245.658797

union

select 123597.599945

union

select 798754.589785

union

select 787561.124657

select substring((cast(amount as char)),0,(charindex('.',(cast(amount as char)))+3)) amount from #temp

amount

1245.65

123597.59

787561.12

798754.58

ARUN SAS

MTV SSC Enthusiast Points: 135 More actions · Answer 7

DECLARE @Num4 AS DECIMAL(10,4)

SET @Num4 = 1234.982644

SELECT LEFT(CONVERT(VARCHAR(20),@Num4),CHARINDEX('.',CONVERT(VARCHAR(20),@Num4))+2)

manjunath5581 SSC Veteran Points: 226 More actions · Answer 8

Hi Madhu,

When my value is like 1567987.4699987 i got the output as

1567987.47 which is actually rounding off the value

DECLARE @Num4 AS DECIMAL(10,4)

SET @Num4 = 1567987.4699987

SELECT LEFT(CONVERT(VARCHAR(20),@Num4),CHARINDEX('.',CONVERT(VARCHAR(20),@Num4))+2)

And the values specified above are just examples not real values.

Hope i am clear in my explaining the problem.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 9

Using ROUND function...

SELECT CONVERT( NUMERIC(18,2), ROUND(1245.65879, 2, 1) )

--Ramesh

MTV SSC Enthusiast Points: 135 More actions · Answer 10

I think that is because of your Decimal declaration. Try this one:-

DECLARE @Num4 AS DECIMAL(30,10)

SET @Num4 = 1567987.4699987

SELECT LEFT(CONVERT(VARCHAR(100),@Num4),CHARINDEX('.',CONVERT(VARCHAR(100),@Num4))+2)

I have changed @Num4 to DECIMAL(30,10) and is working fine.

🙂

manjunath5581 SSC Veteran Points: 226 More actions · Answer 11

Thank you very much for your responses

As i did in Stored procedure is there any way to trim the value to last 2 decimal points in SSRS 2005 reports.

if for example if i have 40524786.406314 i want that to display as

40524789.40.

If i right click on properties and format that to 2 digits it rounds of to

40524789.41

Is there any way to find decimal point and display the right 2 numbers next to the decimal point.

Your help would be greatful..

Thank you

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

manjunath5581 (5/20/2009)
Thank you very much for your responses
As i did in Stored procedure is there any way to trim the value to last 2 decimal points in SSRS 2005 reports.
if for example if i have 40524786.406314 i want that to display as
40524789.40.
If i right click on properties and format that to 2 digits it rounds of to
40524789.41
Is there any way to find decimal point and display the right 2 numbers next to the decimal point.
Your help would be greatful..
Thank you

Lookup ROUND() in BOL (Books On-Line).

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 13

From reporting services you should be able to put in the properties of that database field a format of n2. That should format the number for presentation purposes.

manjunath5581 SSC Veteran Points: 226 More actions · Answer 14

I tried doing that, formatting the value with n2 it rounds to .41 at the end of the sum..

Is there any way to find the decimal point in Expression and trim the 2 numbers right to the decimal point.

if i try to format the number it rounds of my value...