Hello,

I'm currently modifying an old report that we have in our library and was wondering if this line of code is acceptable for defining a code for subtracting expiration dates from the current date.
The following code is used in our DEFINE portion:

  

CURRENT_DATE/YYMD = '&YYMD';
DLEXPDATE/YYMD = DLEXPDATE2;
DL_DAYS_TO_EXP/I5  =  IF DLEXPDATE NE 0 THEN (DLEXPDATE - CURRENT_DATE) ELSE 9999;

We then use that line of code in a WHERE statement to filter out any employee that has an expiration date less than or equal to 30, 60, or 90 days.

 

WHERE TOTAL DL_DAYS_TO_EXP LE &DAYS.(<30 Days,30>,<60 Days,60>,<90 Days,90>).Expires Within.;

 

The issue is that one of the results is returning '-108' days while the others are returning the correct, positive numbers.

This message has been edited. Last edited by: FP Mod Chuck,

You should better use the DATEDIF() function to calculate days between dates.

I would check your data to see what values are in DLEXPDATE.

Ditto to MartinY and Waz... Also verify the formats. Can't -108 be valid?

Hi NVLee

Welcome to Focal Point! It is a great forum for getting quick answers to your development questions...

I agree with Martin that DATEDIF is the way to go..

quote:

The issue is that one of the results is returning '-108' days while the others are returning the correct, positive numbers

What you have to understand is that by doing : DLEXPDATE - CURRENT_DATE you are performing a numerical subtraction such as 15 - 8 which is good while both of your arguments are in the same month (and in the proper order in the equation) otherwise this is the reason why you have negative value. And much more true when your ExpiryDate (DLEXPDATE) is before the current date.

Sample :

20171120 - 20171108 = 12

20171010 - 20171108 = -98 (should be 29 days, not -98)

quote:

Originally posted by Doug:
Ditto to MartinY and Waz... Also verify the formats. Can't -108 be valid?

Indeed. I would think that -108 is a result that is already 108 days past its expiration date. That could very well be a valid result.

quote:

I would think that -108 is a result that is already 108 days past its expiration date.

Could be, but if you look at my previous sample calculation, I doubt that -108 is really 108 days ago...

You might need to check for null values in your data as well. Null's do wierd things

quote:

Null's do wierd things

Ditto to that

Thank you for your help everyone! I've been playing around with the DATEDIF() function as suggested. Based on new information I received recently I am now awaiting if the data in our system is actually correct to begin with since it returned a -108 in our AS400 system as well.