CODEWITHSUNDEEP
sqlsql-serveroracle-databaset-sql
David Smith
David Smith

Reputation: 91

DATEADD not returning correct dates for some rows

The function should return a new date after the current date and the num_of_flows is added. The num_of_flows is an integer value 

ALTER  function [dbo].[M20_22_GetLoanMaturityDate_FIN_10]
(
    @AcctAcid varchar(50),
    @ScheduleNum varchar(50),
    @bank_id varchar(6) = '03'
)
returns varchar(50)
as 
BEGIN
    Declare @lMaturityDate varchar(50)
    begin
            select @lMaturityDate = 
            case Finacle_Lrs.LR_FREQ_TYPE   
                             when 'W' then  dateadd(day, (Finacle_Lrs.NUM_OF_FLOWS - 1) * 7,Finacle_Lrs.FLOW_START_DATE)
                             when 'F' then  dateadd(day, (Finacle_Lrs.NUM_OF_FLOWS - 1) * 14,Finacle_Lrs.FLOW_START_DATE)
                             when 'M' then  (dateadd(month, Finacle_Lrs.NUM_OF_FLOWS - 1, Finacle_Lrs.FLOW_START_DATE))
                             when 'Q' then  (dateadd(month, (Finacle_Lrs.NUM_OF_FLOWS - 1) * 3, Finacle_Lrs.FLOW_START_DATE))
                             when 'H' then  dateadd(month, (Finacle_Lrs.NUM_OF_FLOWS - 1) * 6, Finacle_Lrs.FLOW_START_DATE)
                             when 'Y' then  dateadd(month, (Finacle_Lrs.NUM_OF_FLOWS - 1) * 12, Finacle_Lrs.FLOW_START_DATE)
                      end
            from    [TEST_EIMDW_Archive].[ARCHOWN].[FINCL10_LRS] Finacle_Lrs
            where   Finacle_Lrs.acid = @AcctAcid
            and Finacle_Lrs.shdl_num = @ScheduleNum
            and Finacle_Lrs.FLOW_ID in ('PRDEM','EIDEM')
            AND Finacle_Lrs.ACTIVE_FLAG = 'Y' AND Finacle_Lrs.DELETE_FLAG= 'N'
            and Finacle_Lrs.bank_id = @bank_id
            and Finacle_Lrs.FLOW_START_DATE = ( select max(a.FLOW_START_DATE)
                            from   [TEST_EIMDW_Archive].[ARCHOWN].[FINCL10_LRS] a
                            where  a.ACID = Finacle_Lrs.ACID
                                and    a.SHDL_NUM = Finacle_Lrs.SHDL_NUM
                            and    a.FLOW_ID in ('PRDEM', 'EIDEM') AND a.ACTIVE_FLAG = 'Y' AND a.DELETE_FLAG='N' and a.bank_id = @bank_id
        );
end
        return isnull(@lMaturityDate,null);

END

After doing some checks, I noted that 19629 rows are understated by altest one day when comparaing the orginal code on a oracle database. Please see oracle version of code:

FUNCTION GetLoanMaturityDate(AcctAcid VARCHAR2, ScheduleNum VARCHAR2) RETURN DATE IS
        lMaturityDate DATE;
    BEGIN
        begin
            select decode(lrs.LR_FREQ_TYPE, 'W', lrs.FLOW_START_DATE + (lrs.NUM_OF_FLOWS - 1) * 7,
                            'F', lrs.FLOW_START_DATE + (lrs.NUM_OF_FLOWS - 1) * 14,
                            'M', add_months(lrs.FLOW_START_DATE, lrs.NUM_OF_FLOWS - 1) ,
                            'Q', add_months(lrs.FLOW_START_DATE, (lrs.NUM_OF_FLOWS - 1) * 3),
                            'H', add_months(lrs.FLOW_START_DATE, (lrs.NUM_OF_FLOWS - 1) * 6),
                            'Y', add_months(lrs.FLOW_START_DATE, (lrs.NUM_OF_FLOWS - 1) * 12)
                     )
            into    lMaturityDate
            from    lrs
            where   lrs.acid = AcctAcid
            and lrs.shdl_num = ScheduleNum
            and lrs.FLOW_ID in ('PRDEM','EIDEM')
            and lrs.FLOW_START_DATE = ( select max(a.FLOW_START_DATE)
                            from   lrs a
                            where  a.ACID = lrs.ACID
                                and    a.SHDL_NUM = lrs.SHDL_NUM
                            and    a.FLOW_ID in ('PRDEM', 'EIDEM')
                               ) ;
        exception
            when no_data_found then
                lMaturityDate:=NULL;
        end;

        return lMaturityDate;
    END GetLoanMaturityDate;

Also here is the comparsion query I ran to see the incorrect rows: 

SELECT eim.acid as eim_acid,it.[ACID] as it_acid,
eim.[MATURITY_DATE] as eim_maturity_date,it.[MATURITY_DATE] as it_maturity_date ,
eim.[DAYS_TO_MATURITY] as eim_DAYS_TO_MATURITY,it.[DAYS_TO_MATURITY] as it_DAYS_TO_MATURITY,(it.[DAYS_TO_MATURITY]-eim.[DAYS_TO_MATURITY]) as diff
FROM [TEST_EIMDW_BOJ_REPORTS].[BOJOWN].[loans_maturity_profile] eim 
inner join [dbo].[IT_LOANS_MATURITY_PROFILE] it on eim.acid= it.acid
where eim.MATURITY_DATE != it.MATURITY_DATE

Finally, below is the query result showing the comparison of the correct result set verus the one I currenty trying to fix on sql server:

enter image description here

Upvotes: 3

Views: 378

Answers (3)

Piotr Palka
Piotr Palka

Reputation: 3159

SQL Server "End of month" - EOMONTH (start_date[, month_to_add ]) function will behave similar to ORACLE add_months for the last day of the month.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Oracle's add_months() and SQL Server's dateaddd(month) have different semantics, particularly at the end of the month.

So:

select add_months(date '2019-02-28', 1)
from dual

--> 2019-03-31

(see db<>fiddle)

Whereas:

select dateadd(month, 1, '2019-02-28')

--> 2019-03-28

(see db<>fiddle)

It can be a little tricky to emulate the Oracle behavior in SQL Server. I'm not even sure if that is desirable.

Upvotes: 2

Steve
Steve

Reputation: 960

Just to add to what Gordon said, the exact behaviour of Oracle's ADD_MONTHS function is that if the source date is the last day of its month, then the return date is also the last day of the resulting month (regardless of the length of the month of the source date).

SQL Server has no similar function.

Which implementation is correct, or whether it matters, will depend on your particular circumstances.

Upvotes: 2

Related Questions