Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problem calculating number of days between two dates

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Problem calculating number of days between two dates
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 7.1.4
Windows, All Outputs
 
Posts: 35 | Registered: December 21, 2011Report This Post
Member
posted Hide Post
It is Define, not a Compute, so you need to remove the word Compute from your code.
 
Posts: 8 | Location: Spain | Registered: November 05, 2010Report This Post
Gold member
posted Hide Post
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


WF 7.6.10, Windows, PDF, Excel
 
Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004Report This Post
Virtuoso
posted Hide Post
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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Silver Member
posted Hide Post
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.


WebFOCUS 7.1.4
Windows, All Outputs
 
Posts: 35 | Registered: December 21, 2011Report This Post
Virtuoso
posted Hide Post
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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
I hope someone else will make a better job at explaining the difference than what I attempted to do in my previous post. Red Face Sorry.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Silver Member
posted Hide Post
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!


WebFOCUS 7.1.4
Windows, All Outputs
 
Posts: 35 | Registered: December 21, 2011Report This Post
Silver Member
posted Hide Post
njsden thank you for your explanation I could understand it!


WebFOCUS 7.1.4
Windows, All Outputs
 
Posts: 35 | Registered: December 21, 2011Report This Post
Virtuoso
posted Hide Post
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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Master
posted Hide Post
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,


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
What a difference a "slight" change in terminology can make.

Thanks jgelona for the very clear explanation. Smiler



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problem calculating number of days between two dates

Copyright © 1996-2020 Information Builders