Search code examples
sqlsybase

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';

enter image description here

I get: 13.639999

I want the value to be : 13.6300 instead of 13.639999

I tried floor but did not work.

Solution

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