How to truncate real, Money or decimal values in sybase database

I have a database function called:
dbo.cf_unpaid_interest_to_date

CREATE FUNCTION dbo.cf_unpaid_interest_to_date(@payment_plan guid_no_default)
returns REAL
AS
BEGIN
declare @runningdate datetime
declare @unpaidInterest REAL
declare @docAmt REAL
declare @type int
set @docAmt = 0
set @unpaidInterest = 0
 declare subread cursor
    for
        select tran_type, doc_amt, datestamp from cf_debtor_transaction 
        where payment_plan_guid = @payment_plan and tran_type in (4243,4743, 4751, 4251, 4423,4123,4112,4612) 
        and datestamp >= (select min(datestamp) from cf_debtor_transaction where payment_plan_guid = @payment_plan and tran_type = 4243) order by datestamp
open subread 

fetch subread into 
        @type, 
        @docAmt, 
        @runningdate
        while (@@sqlstatus=0)
     begin
      set @unpaidInterest = @unpaidInterest + @docAmt
      if(@unpaidInterest <= 0) set @unpaidInterest = 0 
      fetch subread into @type, @docAmt, @runningdate
     end
     return @unpaidInterest
end

I ran the following:

select func_cf_initiation_fee_not_yet_due, func_cf_unpaid_interest,func_cf_payment_plan_balance,installment, 
* from view_cf_payment_plan where account_guid ='0291507300';

I get: 13.639999

I want the value to be : 13.6300 instead of 13.639999

I tried floor but did not work.

Answer

If you want fixed number of decimals, use DECIMAL(precision, scale) datatype instead of REAL. With REAL you are bound to have rounding errors.

Advertisement

Answer