Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to convert to two decimal places Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 5:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 187, Visits: 316
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?
Post #1381020
Posted Monday, November 5, 2012 5:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
You will need to convert it to a data type which allows you to specify the amount of decimal places you want, you would want decimal with the correct precision to cope with how ever many decimal places you need.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1381021
Posted Monday, November 5, 2012 5:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
is your chargeAmount is INT then you need to convert it to decimal to get decimal in SUM
Post #1381023
Posted Monday, November 5, 2012 5:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 187, Visits: 316
My charge amount need to cater for both "Then" and "Else", my "else" returns string hence I cast it as varchar. that code so far is working but I need to return two decimal value amount.
Post #1381031
Posted Monday, November 5, 2012 5:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
cast the inner sum as decimal then case the whole output as varchar



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1381032
Posted Monday, November 5, 2012 6:09 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
Use STR function. E.g:

declare @x money=12345.69851
SELECT [STR]=STR(@x, 20, 2), [CONVERT_default]=CONVERT(varchar(20), @x), [CONVERT_format_1]=CONVERT(varchar(20), @x, 1), [CONVERT_format_2]=CONVERT(varchar(20), @x, 2)



_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1381332
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse