How to calculate days from date field

[boydad]

I am functional report writer in need of assistance in coding the calculation of days in webFOCUS. I am trying to pull SARAPPD_APDC_DATE GT 2 wks/10 BUSDAYS. The SARAPPD_APDC_DATE is formatted as YYYY/MM/DD 00:00:00. I have been working on this for two days without success and encountering FOC error messages that I don’t quite understand. Of course the FOC errors don’t really explain what the problem is! Your help will be greatly appreciated.

Product: Developer Studio (7.6.2)

 

[focwizard]

First off, I'm guessing that your date is a date-time format (HYYMD...). Can you confirm the USAGE format of the field SARAPPD_APDC_DATE?

Second, do you want 2 weeks or 10 business days, and from WHAT date?

If business days, have you properly set the BUSDAYS value (SET BUSDAYS=...), so the product knows WHAT is a business day. AND, is your holiday file established, so it knows what days are holidays?

Assuming everything is set correctly, here's sample code to get what you want:

DEFINE FILE CAR
-* the next 3 define are to get a date-time field
IDATE/I8YYMD = DECODE COUNTRY (ENGLAND 20080101 FRANCE 20080201 ITALY 20080301
                               JAPAN   20080401 ELSE 20080501);
DATE/YYMD = IDATE;
HDATE/HYYMD = HDTTM(DATE,8,'HYYMD');
-*get the date portion of the date-time field
XDATE/YYMD = HDATE(HDATE,'YYMD');
-* geto today into a date field
TODAY/YYMD = '&YYMD';
-* find the difference in business days
DIFF/I4 = DATEDIF(XDATE,TODAY,'BD');
END
TABLE FILE CAR
PRINT TODAY HDATE XDATE DIFF 
BY COUNTRY
END

This produces:

COUNTRY     TODAY       HDATE       XDATE       DIFF
-------     -----       -----       -----       ----
ENGLAND     2008/07/23  2008/01/01  2008/01/01   146
FRANCE      2008/07/23  2008/02/01  2008/02/01   123
ITALY       2008/07/23  2008/03/01  2008/03/01   102
JAPAN       2008/07/23  2008/04/01  2008/04/01    81
W GERMANY   2008/07/23  2008/05/01  2008/05/01    59

 

[boydad]

Thank you so much for your help! My biggest problem that caused the errors was that I needed to redefine the date format for APDC_DECISION_DATE1. Your example was a great help!


APDC_DECISION_DATE/YYMD=HDATE(APDC_DECISION_DATE1, 'YYMD');