Problems with formatting a calculated column

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

General SQL Server Forums

New to SQL Server Programming

Problems with formatting a calculated column

Author

Topic

rico33126
Starting Member

2 Posts

Posted - 2012-10-11 : 10:14:23

Hi there and thanks in advance for your help.

I have a table with an amount and rate columns.

I am creating a view based on this table and I want to add a calculated column that will provide the result between the amount and the rate columns.

SELECT dbo.Invoices.Amount,
dbo.Invoices.TransactionRate,
dbo.Invoices.Amount * dbo.Invoices.TransactionRate AS Trans_Amt
FROM dbo.Properties

The query and the creation of the Trans_Amt column works fine.

The problem is that the calculated column has too many decimal places and I do not know how to format the calculated column to show only 2 decimal places.

Regards

Rio

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-11 : 10:18:50

You can cast it to decimal or use round function.

SELECT dbo.Invoices.Amount,
       dbo.Invoices.TransactionRate,
       CAST(dbo.Invoices.Amount * dbo.Invoices.TransactionRate AS DECIMAL(19,2)) AS Trans_Amt
FROM   dbo.Properties

rico33126
Starting Member

2 Posts

Posted - 2012-10-11 : 11:15:37

quote:

Originally posted by sunitabeck

You can cast it to decimal or use round function.

SELECT dbo.Invoices.Amount,
       dbo.Invoices.TransactionRate,
       CAST(dbo.Invoices.Amount * dbo.Invoices.TransactionRate AS DECIMAL(19,2)) AS Trans_Amt
FROM   dbo.Properties

It worked like a charmed.

How can I display the result of the query in a 3,256.56 format

Thanks again

Rio

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-11 : 11:34:59

Here is an example - but many people on this forum (including yours truly) would advise and would prefer to do this type of formatting on the front-end - such as the client GUI or reporting services.

DECLARE @x FLOAT  ;
SET @x = 232123445.73;
SELECT CONVERT(varchar(32),CAST(@x AS MONEY),1);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources