SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How Convert to two decimal		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Starting Member

42 Posts
Posted - 11/05/2012 :  07:29:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/05/2012 :  07:35:03  Show Profile  Reply with Quote
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))
Edited by - sunitabeck on 11/05/2012 07:35:40
Go to Top of Page

stahorse
Starting Member

42 Posts
Posted - 11/05/2012 :  07:49:58  Show Profile  Reply with Quote
I still need to get it as varchar, to satisfy the else statement
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 11/05/2012 :  07:58:16  Show Profile  Reply with Quote
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
Go to Top of Page

raju5310
Starting Member

India
1 Posts
Posted - 11/06/2012 :  01:11:20  Show Profile  Reply with Quote
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
Go to Top of Page

stahorse
Starting Member

42 Posts
Posted - 11/06/2012 :  01:44:33  Show Profile  Reply with Quote
The requirements states that if chargeType is not 'ADV_SWITCH_FEE' then i must return blank, hence "else ''"
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 11/06/2012 :  03:54:55  Show Profile  Reply with Quote

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

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 11/06/2012 :  12:20:43  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000