How Convert to two decimal - 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

SQL Server 2008 Forums

Transact-SQL (2008)

How Convert to two decimal

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-11-05 : 07:29:58

Hi

I have this code here

case
when chargeType <> 'ADV_SWITCH_FEE'
then CAST(isNull(sum(chargeAmount)/100.0, 0.0) AS VARCHAR)
else ''
end as chargeAmount

How do I convert my "sum" into decimal places?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-05 : 07:35:03

Use one or the other below

CAST (ISNULL(SUM(chargeAmount) / 100.0, 0.0) AS DECIMAL(18,2))

LTRIM(STR(ISNULL(SUM(chargeAmount) / 100.0, 0.0),10,2))

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-11-05 : 07:49:58

I still need to get it as varchar, to satisfy the else statement

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 07:58:16

quote:
Originally posted by stahorse

I still need to get it as varchar, to satisfy the else statement

case
when chargeType <> 'ADV_SWITCH_FEE'
then CAST (ISNULL(SUM(chargeAmount) / 100.0, 0.0) AS DECIMAL(18,2))
else 0.00
end as chargeAmount

--
Chandu

raju5310
Starting Member

1 Post

Posted - 2012-11-06 : 01:11:20

case
when chargeType <> 'ADV_SWITCH_FEE'
then CAST (ISNULL(SUM(chargeAmount) / 100.0, 0.0) AS DECIMAL(18,2))
else 0.00
end as chargeAmount

raj

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-11-06 : 01:44:33

The requirements states that if chargeType is not 'ADV_SWITCH_FEE' then i must return blank, hence "else ''"

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-06 : 03:54:55

CAST(CAST (ISNULL(SUM(chargeAmount) / 100.0, 0.0) AS DECIMAL(18,2)) AS VARCHAR(25))
else ''

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 12:20:43

quote:
Originally posted by stahorse

The requirements states that if chargeType is not 'ADV_SWITCH_FEE' then i must return blank, hence "else ''"

why not return it as decimal itself and do this formatting at presentation layer? it doesnt make sense to return decimal as varchar for sql especially if you want to do some further manipulations using this value like sorting etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources