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
 General SQL Server Forums
 New to SQL Server Programming
 Problems with formatting a calculated column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rico33126
Starting Member

2 Posts

Posted - 10/11/2012 :  10:14:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/11/2012 :  10:18:50  Show Profile  Reply with Quote
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
Go to Top of Page

rico33126
Starting Member

2 Posts

Posted - 10/11/2012 :  11:15:37  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/11/2012 :  11:34:59  Show Profile  Reply with Quote
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);
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.08 seconds. Powered By: Snitz Forums 2000