This should return 73.34...
However, it returns a 0 instead.
select convert(decimal(18,2),(3370)/(4595)*100)
Can you pl advise.
Either add a .0 to the end of the numbers, or explicitly convert to float.
The reason is that SQL Server, when dividing integers, returns an integer.
select convert(decimal(18,2),(3370.0)/(4595.0)*100.0) returns 73.34
BobP
|||To add to Bob's comment:
IF both the dividend and divisor are whole numbers (int), SQL assumes you want the results as an int.
IF either of the two contains a decimal, a float will be returned. For example:
|||
SELECT (( 3370./4595 ) * 100.0 )
--
73.3405000
The datatype on the field, acctno is Int.. I use the convert statement to convert to float. but it still returns the data in INt ie it return 3370 instead of 3370.0.. Though I AM using covnert to float, data is still returned as an INT.
Select round(count(convert(float,convert(decimal(38,5),acctno))),2) from tbl1
WHERE (tbl1.balance<>0).
Any ideas how I can return The value as 3370.0
|||Count returns an int... so there is no decimal.
Use convert(float, count(...
BobP
|||Whoa nellie! What's with the round(), count() convert(), convert(), etc.
There is reason to convert(), round(), etc., it you are only interested in the count of rows meeting the criteria.
SELECT cast( count( AcctNo ) AS decimal(10,2))
FROM tbl1
WHERE tbl1.Balance <> 0
'should' do the trick.
No comments:
Post a Comment