Remove digits after decimal after ROUND()

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

SQL Server 2008 Forums

Transact-SQL (2008)

Remove digits after decimal after ROUND()

Author

Topic

learntsql

524 Posts

Posted - 2010-09-13 : 07:06:20

Hi All,
I have one column with decimal(38,20) declared.
For some values i have to show like integers and some like decimals based on condition;but originally all are stored as decimal format only.

for eg;
Value Category
23.346 int
456.478 normal

for all the "int" category values truncate upto decimal point
and normal category display as it is.

TIA.

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 07:11:29

select convert(int,23.346 ),23.346

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

learntsql

524 Posts

Posted - 2010-09-13 : 07:15:47

Thanks,
Wonderful....

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 07:17:30

quote:
Originally posted by learntsql

Thanks,
Wonderful....

Welcome

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

learntsql

524 Posts

Posted - 2010-09-13 : 07:22:07

Sorry I have one query in this..
in my Query am applying like
CASE WHEN Category='int' THEN CONVERT(int,Value) ELSE CONVERT(decimal(38,2),Value)

in output for int category also its showing with 2 decimal values...
.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-13 : 07:22:14

You can't use this solution if you are going to display the two different kind of data in same column.

N 56°04'39.26"
E 12°55'05.63"

learntsql

524 Posts

Posted - 2010-09-13 : 07:28:59

alternative plz.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-13 : 09:22:37

Your ONLY other option is to CONVERT the data into varchar. You really should do this conversion within your application.

CASE WHEN Category = 'int' THEN STR(Value, 35, 0) ELSE STR(Value, 38, 2) END

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources