How to convert to two decimal places

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How to convert to two decimal places

How to convert to two decimal places

Rate Topic

Display Mode

Topic Options

Author

Message

hoseam

Posted Monday, November 05, 2012 5:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:16 AM
Points: 36, Visits: 65

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

anthony.green

Posted Monday, November 05, 2012 5:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

BriPan

Posted Monday, November 05, 2012 5:36 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 01, 2013 6:16 AM
Points: 81, Visits: 286

is your chargeAmount is INT then you need to convert it to decimal to get decimal in SUM

Post #1381023

hoseam

Posted Monday, November 05, 2012 5:51 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:16 AM
Points: 36, Visits: 65

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

anthony.green

Posted Monday, November 05, 2012 5:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

cast the inner sum as decimal then case the whole output as varchar

Post #1381032

Vedran Kesegic

Posted Monday, November 05, 2012 6:09 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

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)

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1381332

« Prev Topic | Next Topic »

Permissions