Hi guys, I got a problem and I hope yo can help me.
In my program, I have a DEFINE section where I want to calculate the number of days between two dates just like this

  
DEFINE FILE ACCOUNTS
START_DATE/A8DMYY = START_DATE;
END_DATE/A8DMYY = END_DATE;
COMPUTE QTDAYS/I14     = DATEDIF(END_DATE,START_DATE,'WD');
END

But I'm getting this error:

 
(FOC259) FIELDNAME EXCEEDS ITS MAXIMUM SIZE: COMPUTE QTDIAS

What is wrong with my code?

This message has been edited. Last edited by: Kerry,

It is Define, not a Compute, so you need to remove the word Compute from your code.

1. To add to JAlbarran's comment, the format size of Integer is 11.
2. I found the DATEDIF likes Smart Dates.

I got this to work:

  
DEFINE FILE EMPLOYEE
START_DTE/A8YYMD = '20120110';
START_DATE/YYMD  = START_DTE;
END_DTE/A8YYMD   = '20120131';
END_DATE/YYMD    = END_DTE;
QTDAYS/I11       = DATEDIF(END_DATE,START_DATE,'WD');
END

bohorkez, DATEDIF needs to operate on actual date fields (YYMD) and not on SmartDates (A8YYMD) as you seem to pretend doing in your code.

What original format are your ACCOUNTS.START_DATE and ACCOUNTS.END_DATE defined as in the masterfile?

If they are DATES (YYMD) then you should be fine.

If they are TIMESTAMPS (HYYMDS, for instance) then you'll have to convert them to DATES before you can use DATEDIF.


Please check the product's documentation for DATEDIF, HDATE and/or DATECVT function to get an understanding about how to operate on dates and you should then be able to figure out a solution to your particular case.

I'm going to try your solutions don't get very well what's the main difference between SMARTDATES and DATES.
Thank for your answers, before I post my question I checked the documentation manual specially the WEBFOCUS function reference where I find information about DATEDIF because I didn't know that it was a function to calculate the number of days in two dates.

Based on my understanding, a SmartDate is nothing but a number that "looks" like a date and can be interpreted as such by WebFOCUS.

For instance, having a field as:

MYDATE/I8YYMD = 20120110;

It's actually an Integer (see the I8 prefix in the data type) and may very well be seen as 20,120,110. FOCUS can actually use that integer and interpret as a DATE as I think that's what used to be done in mainframe environments before support for "native" dates was available.

A DATE field on the contrary is defined as:

MYDATE/YYMD = 20120110;

Even though we provide a "numeric" value and WebFOCUS is able to render it as 2012/01/10, it internally uses a different number to represent that date (I think it specifies the number of days since Jan 1, 1900 or something like that).

Functions such as DATEDIF, DATEMOV, DATEADD, etc. expect DATE values, that is, values that are internally represented as a DATE as expected by WebFOCUS regardless of how they are presented to you.

That is why your A8YYMD would not be a good candidate for DATEDIF as it is not a "native" DATE but an alphanumeric value that looks like a DATE ... in other words, a SmartDate.

I hope someone else will make a better job at explaining the difference than what I attempted to do in my previous post. Sorry.

I could resolve my problem. I used this code

DEFINE FILE ACCOUNTS
START_DATE/A8DMYY = START_DATE;
END_DATE/A8DMYY =   END_DATE;
START_DATE/DMYY   = START_DATE;
END_DATE/DMYY   = END_DATE;
QTDAYS/I11     = DATEDIF(END_DATE,START_DATE,'WD');
END

I received START_DATE and END_DATE as A8 for that reason I had to do the conversion.Now is working fine.

Thank you everyone for the support!

njsden thank you for your explanation I could understand it!

quote:

What original format are your ACCOUNTS.START_DATE and ACCOUNTS.END_DATE defined as in the masterfile?

quote:

I received START_DATE and END_DATE as A8

bohorquez, if that's the case you could probably get away with just:

DEFINE FILE ACCOUNTS
QTDAYS/I11 = DATEDIF (DATECVT(END_DATE,   'A8YYMD','YYMD'), 
                      DATECVT(START_DATE, 'A8YYMD','YYMD'), 
                      'WD');
END

Some of the above is correct and some of it is not.

Not including datetime stamps, FOCUS/WebFOCUS recognizes 2 types of dates, Legacy Dates and Smart Dates.

Legacy Dates are of the formats, A8MDYY, A8YYMD, A6YMD, I8YYMD, etc. Any date format that starts with 'I' or 'A' is a Legacy Date. Internally, the 'A' formats are an Alphanumeric string and the 'I' formats are integers. So if the format is A8YYMD, Jan 1 2012 is '20120101' and if the format is I8YYMD, the value is the integer 20120101.

Smart Dates are of the format MDYY, YYMD, YMD, DMYY. Internally, these are stored as an integer. However, the integer is the number of days from the 0 date which is 12/31/1900. So a value of 1 is really Jan 1 1901, 2 is Jan 2 1901, -1 is Dec 30, 1900. What this means is the MDYY, YYMD, YMD, etc. are only display formats. What this also means is that subtracting 1 date from another to find the number of days between to dates or adding/substracting days to/from a date is much easier since it is just integer arithmetic. For example, if you have 2 Smart Dates, to find the number of days between the 2 dates is just "DAYS/I8=SDATE1-SDATE2;"

If you need more than this, then use the functions DATEDIF, DATEMOV, DATEADD, etc. These work only with Smart Dates. This is well documented.

Also well documented are the Legacy Date functions.

Actually, I almost never have any reason for using Legacy Date Formats. If I have a .mas file where a date is Legacy Date, I will change the master to show a Smart Date either by change the field in the .mas or by adding a DEFINE in the .mas. For example,

I will change:
  FIELD=LDATE,,USAGE=A8YYMD,ACTUAL=A8,$
to
  FIELD=LDATE,,USAGE=YYMD,ACTUAL=A8,$

OR add a DEFINE at the end of the segment:

  DEFINE SDATE/YYMD=LDATE;

In our .mas files that describe Oracle tables, all our Oracle date fields are internally datetime stamps I create 2 fields for the same oracle field.

  FIELD=AS_BEGDT    , STRT_DT           , YYMD  , DATE              ,$
  FIELD=AS_BEGDTTM  , STRT_DT           , HYYMDS, HYYMDS            ,$
  FIELD=AS_ENDDT    , END_DT            , YYMD  , DATE  , MISSING=ON,$
  FIELD=AS_ENDDTTM  , END_DT            , HYYMDS, HYYMDS, MISSING=ON,$

This way WebFCOUS lets me treat the same Oracle datetime stamp as a Smart Date or as a Datetime Stamp. If I need the # days between AS_ENDDT and AS_BEGDT I can use the standard method or DATEDIF but if I need to be more exact, like the number of Days, Hours and minutes, between the 2 dates, I can use HDIFF to find the # of minutes between AS_ENDDTTM and AS_BEGDTTM and calculate Days, Hours and Minutes.

This message has been edited. Last edited by: jgelona,

What a difference a "slight" change in terminology can make.

Thanks jgelona for the very clear explanation.