Getting undesirable trailing zeros after casting t

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

SQL Server 2008 Forums

Transact-SQL (2008)

Getting undesirable trailing zeros after casting t

Author

Topic

Darts75
Starting Member

27 Posts

Posted - 2014-09-07 : 22:00:24

Hi Everyone,

The following line of SQL returns the first screen shot below.


((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS 'Gross Profit %'

I now want to limit the decimal places to two (2), and one might think that using a CAST operation here is the solution, as follows.


CAST(((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS decimal(15,2)) AS 'Gross Profit %'

However in actuality I am still seeing too many decimal places, but the extra ones have changed to ZEROS!

Ideally I would like to have no extra trailing zeros. If you have any suggestions regarding how to achieve this can you please let me know?

Kind Regards,

Davo

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-09-08 : 06:08:42

did you try using ROUND function?

Javeed Ahmed

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-08 : 12:59:23

I can't reproduce your result. I tried this:


with T0(TotalSales, StockValue) as (
	select 200.123456789, 22.123456789),
	T1(TotalSales, StockValue) as (
	select 15.123456788, 150.1234567789)
	


select
CAST(((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS decimal(15,2)) AS 'Gross Profit %'
from t0, t1

I got this result:


Gross Profit %
---------------------------------------
-244.53

(1 row(s) affected)

with just two decimal places

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources