Calculating Date Difference in Days

Kitui · 2018-08-11

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

Slawek_Guzek · 2018-08-11

The DATE2DMY throws such an error when the date parameter passed to it is empty (0D). The "Last Pay Date" must be empty in one of records you are processing.

You can simplify your code like this:

IF "Last Pay Date" > CALCDATE('<-1M>', TODAY) THEN  
  "Loans Register"."Loans Category":="Loans Register"."Loans Category"::Perfoming
ELSE
  //non-performing

that will go to ELSE section if the "Last Pay Date" is blank. And it works when dates crosses a year boundary.

Slawek_Guzek · 2018-08-11

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) )

kriki · 2018-08-14

[Topic moved from 'General Chat' forum to 'NAV Three Tier' forum]

Slawek_Guzek · 2018-08-11

The DATE2DMY throws such an error when the date parameter passed to it is empty (0D). The "Last Pay Date" must be empty in one of records you are processing.

You can simplify your code like this:

IF "Last Pay Date" > CALCDATE('<-1M>', TODAY) THEN  
  "Loans Register"."Loans Category":="Loans Register"."Loans Category"::Perfoming
ELSE
  //non-performing

that will go to ELSE section if the "Last Pay Date" is blank. And it works when dates crosses a year boundary.

Kitui · 2018-08-11

And if i setfilter where last pay day isnt nill.

Slawek_Guzek · 2018-08-11

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) )

kriki · 2018-08-14

[Topic moved from 'General Chat' forum to 'NAV Three Tier' forum]

Calculating Date Difference in Days

Best Answers

Answers

Categories