Calculating Date Difference in Days

KituiKitui Posts: 2Member
Iam doing a report that classifies loans based on the last payment day and today.If last payment day is less than a month it is classified as performing.Did the following code but an error pops 'Date is Invalid' when i run the report.

Day1:=DATE2DMY("Last Pay Date",1);
Month1:=DATE2DMY("Last Pay Date",2);
Year1:=DATE2DMY("Last Pay Date",3);

Day2:=DATE2DMY(TODAY,1);
Month2:=DATE2DMY(TODAY,2);
Year2:=DATE2DMY(TODAY,3);
IF (Year1=Year2) AND ((Month2-Month1)<=1) AND (( Day2-Day1)<30) THEN
"Loans Register"."Loans Category":="Loans Register"."Loans Category"::Perfoming
ELSE

Best Answers

Answers

  • KituiKitui Posts: 2Member
    And if i setfilter where last pay day isnt nill.
  • Slawek_GuzekSlawek_Guzek Posts: 1,596Member
    If you keep your code and add filter excluding records with empty "Last Pay Date" it will not throw any errors, but it will incorrectly classify loans when your code will be running in January - every year;

    Doing calculations directly on TODAY's and "Last Pay Date" field prevents you from doing any real testing. What you should do is to create a function with two dates as input parameters, and boolean as output, and encapsulate calculations in this function:
    IsLoanPerforming(PaymentDate Date, TestDate Date) : Boolean
    {
      IF TestDate = 0D THEN
        EXITFALSE); //or return TRUE or ERROR incorrect input parameter - whatever rule suits your busines
      EXIT( PaymentDate > CALCDATE('<-1M>', TestDate) );
    }
    
    or if you wish to keep your code:
    IsLoanPerforming(PaymentDate Date, TestDate Date) : Boolean
    {
      Day1:=DATE2DMY("Last Pay Date",1); 
      Month1:=DATE2DMY("Last Pay Date",2);
      Year1:=DATE2DMY("Last Pay Date",3);
    
      Day2:=DATE2DMY(TODAY,1);
      Month2:=DATE2DMY(TODAY,2);
      Year2:=DATE2DMY(TODAY,3);
      EXIT(
        (Year1=Year2) AND ((Month2-Month1)<=1) AND (( Day2-Day1)<30) 
      );
    }
    
    This way you can make your code more understandable :
    IF IsLoanPerforming("Last Pay Date", TODAY) THEN  
      "Loans Register"."Loans Category":="Loans Register"."Loans Category"::Perfoming
    ELSE
      //non-performing
    
    and also you could test a few different date combinations to make sure the code works in all cases
    somePaymentDate ;= 01012018;
    someTestDate := 02012018;   
    MESSAGE( 'Payment date:%1, Test Date: %2, Loan Performing : %3',
      somePaymentDate , someTestDate, IsLoanPerforming(somePaymentDate, someTestDate) );
    
    somePaymentDate ;= 31122017;
    someTestDate := 02012018;   
    MESSAGE( 'Payment date:%1, Test Date: %2, Loan Performing : %3',
      somePaymentDate , someTestDate, IsLoanPerforming(somePaymentDate, someTestDate) )
    
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.