CAST question - SQL Server Forums

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

CAST question

Author

Topic

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-17 : 15:26:04

List all resellers and total sales amount for each.
-- Show Reseller name, business type, and total sales with the sales showing two decimal places.
-- Be sure to include resellers for which there are no sales.
-- 701 Rows

SELECT R.ResellerName,BusinessType, CAST(R.AnnualSales AS decimal(8,2)) AS TotalSales
FROM dbo.DimReseller AS R
WHERE AnnualSales >= 0

I guess I have to use CONVERT right? Also, how do I include resellers for which there are no sales?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-17 : 15:35:55

You can use CONVERT or ROUND - but the recommended practice is to do the formatting at the presentation layer (such as reporting services, or asp.net page) if you have one.

Remove the AnnualSales >= 0 clause to show resellers who have had no sales.

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-17 : 15:40:18

SO TO DISPLAY 2 DECIMALS USING CONVERT, HOW DO YOU DO THAT?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-17 : 16:46:33

See the examples below - except for the first they all convert the number to 2 decimals. The last one returns a string and that is less desirable for a variety of reasons

DECLARE @x FLOAT = 3.14159265456;
SELECT @x
SELECT ROUND(@x,2);
SELECT CAST(@x AS DECIMAL(18,2));
SELECT LTRIM(STR(@x,32,2));

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-17 : 17:32:19

I got it now. CONVERT(DECIMAL(10,2),R.AnnualSales)

thx though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources