Truncate decimal places - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Truncate decimal places

Author

Topic

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 11:13:53

Can you plz tell me how to truncate zeros after decimal

the case is like this

round(992.55,0)
result =993.00

round(993.888)
result = 994.000

I dont want to pass decimal spaces,if i want to use 'right' i dont know how many decimal places are going to be.

And also the value 992.55 is in @amount

can we use round(@amount,0) ?

Thanks fror ur help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 11:17:26

[code]SELECT round(992.55, 0) -- returns 993.00
SELECT round(993.888, 0) -- returns 994.000[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 11:18:24

[code]DECLARE @Amt DECIMAL(17, 6)

SET @Amt = 992.55

SELECT ROUND(@Amt, 0) -- returns 993.000000[/code]

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

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 11:24:57

i would like to pass only 932 from 932.000 and 932.00000,

no decimal places after rounding

Thanks

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 11:27:07

i would like to truncate any number of zeros after decimal

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 11:30:01

CAST (@Amt AS INT)

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

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 11:55:59

i would like to truncate any number of zeros after decimal

932.000 or 932.000000 to 932

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 11:59:16

CAST (@Amt AS INT)

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

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 13:16:57

DECLARE @Amt DECIMAL(17, 6)
SET @Amt = 992.55
set @amt = ROUND(@Amt, 0)
CAST (@Amt AS INT)

I am getting error neat @amt neat CAST

I tried like decimal(17,0)

DECLARE @Amt DECIMAL(17, 0)
SET @Amt = 99288.55777
set @amt = ROUND(@Amt, 0)
select @amt
CAST (@Amt AS INT)

result:99289

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 13:18:04

DECLARE @Amt DECIMAL(17, 6)
SET @Amt = 992.55
set @amt = ROUND(@Amt, 0)
CAST (@Amt AS INT)

I am getting error neat @amt neat CAST

I tried like decimal(17,0)

DECLARE @Amt DECIMAL(17, 0)
SET @Amt = 99288.55777
set @amt = ROUND(@Amt, 0)
select @amt

result:99289

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 13:21:35

the problem if if i declare decimal data type....this value should be passed to a table ,there the column data type is numeric.. so there may be problem

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-25 : 13:25:05

Are you trying to get rid of the decimal portion? For example 123.456 becomes 123 and 456.789 becomes 456?
If so, do what Peso said at 11:30 and 11:59

DECLARE @Amt DECIMAL(17, 6)
SET @Amt = 992.55
SELECT CONVERT(int,@Amt)

Jim

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 13:25:57

i found that even if we use numeric(10,0) we dont display decimals

DECLARE @Amt numeric(10,0)
SET @Amt = 99288.55777
set @amt = ROUND(@Amt, 0)
select @amt

result :99289

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 13:28:03

quote:
Originally posted by Chinni

the problem if if i declare decimal data type....this value should be passed to a table ,there the column data type is numeric.. so there may be problem

decimal and numeric are similar datatypes, so that wont cause any problem. Why didnt you tried casting to int as Peso suggested? what happned when you tried this?

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 14:08:36

DECLARE @Amt DECIMAL(17, 6)
SET @Amt = 992.55
set @amt = ROUND(@Amt, 0)
CAST (@Amt AS INT)

I am getting error near @amt neat CAST

I dont want any zero after decimal...result shd look like 933

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-25 : 15:24:33

Again, Peso already gave you the answer..

quote:
Originally posted by Chinni

DECLARE @Amt DECIMAL(17, 6)
SET @Amt = 992.55
~~set @amt = ROUND(@Amt, 0)~~
SELECT CAST (@Amt AS INT)

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-08-25 : 15:53:27

DECLARE @Amt DECIMAL(17, 6)
SET @Amt = 992.1544
set @amt = ROUND(@Amt, 0)
select CAST (@Amt AS INT)

Thanks a lot all and thanks Peso ...its working...........

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-26 : 01:54:39

Thank you for the feedback.

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

eclipce051
Starting Member

5 Posts

Posted - 2008-09-18 : 15:06:32

round(@amt,0,1)

it will truncate the decimals

ex. 48.4848

exec 48.0000

Life is short get a rest and go out with some chiks :P

eclipce051
Starting Member

5 Posts

Posted - 2008-09-18 : 17:53:19

IF you change the round number you can make it to that especific decimal...

zero decimal
ex. round(@amt,0,1)
exec 48.0000

1 decimal space
ex. round(@amt,1,1)
exec 48.4000

2 decimal space
ex. round(@amt,2,1)
exec 48.4800

3 decimal space
ex. round(@amt,3,1)
exec 48.4840

4 decimal space
ex. round(@amt,4,1)
exec 48.4848

hope this helps us out :p

Life is short get a rest and go out with some chiks :P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources