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     How to find day difference between to I8YYMD fields?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How to find day difference between to I8YYMD fields?
 Login/Join
 
Master
posted
I have these two dates defined...
DUE_DATE/I8YYMD=DUE;
RCVD_DATE/I8YYMD=DATE;

when I try to do this
LEAD_TEST/D20 = DATEDIF(DUE_DATE, RCVD_DATE, 'days');

I get a
INVALID TYPE OF ARGUMENT #2 FOR USER FUNCTION DATEDIF

is there a problem with the I8YYMD field? I do the datedif on &YMD and it works fine!


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Expert
posted Hide Post
quote:
DATEDIF

The dates should be defined as Date fields, i.e. /YYMD


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
Jason,

What do the fields look like in the master? In particular what is the ACTUAL format? The reason I ask is that depending on the ACTUAL, you may be able to change the FORMAT to YYMD.

Another option is:
LEAD_TEST/D20=DATEDIF(DATECVT(DUE_DATE,'I8YYMD','YYMD'), DATECVT(RCVD_DATE,'I8YYMD','YYMD'), 'days');


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
If the format is a HYYMD type (so with hours and minutes) you also can use the formula HDIFF

Finding the Number of Days Between Two Date-Time Fields (Reporting)
HDIFF calculates the number of days between the TRANSDATE and ADD_MONTH
stores the result in DIFF_PAYS, which has the format D12.2:

TABLE FILE VIDEOTR2
PRINT CUSTID TRANSDATE AS 'DATE-TIME' AND COMPUTE
ADD_MONTH/HYYMDS = HADD(TRANSDATE, 'MONTH', 2, 8, 'HYYMDS');
DIFF_DAYS/D12.2 = HDIFF(ADD_MONTH, TRANSDATE, 'DAY', 'D12.2');
WHERE DATE EQ 2000;
END
The output is:
CUSTID DATE-TIME ADD_MONTH DIFF_DAYS
------ --------- --------- ---------
1237 2000/02/05 03:30 2000/04/05 03:30:00 60.00
1118 2000/06/26 05:45 2000/08/26 05:45:00 61.00



if the format is a legacy date (as Francis already said)

Finding the Number of Weekdays Between Two Dates (Reporting)
DATECVT converts the legacy dates in HIRE_DATE and DAT_INC to the date format YYMD.
DATEDIF then uses those date formats to determine the number of weekdays between
NEW_HIRE_DATE and NEW_DAT_INC:
TABLE FILE EMPLOYEE
PRINT FIRST_NAME AND
COMPUTE NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD'); AND
COMPUTE NEW_DAT_INC/YYMD = DATECVT(DAT_INC, 'I6YMD', 'YYMD'); AND
COMPUTE WDAYS_HIRED/I8 = DATEDIF(NEW_HIRE_DATE, NEW_DAT_INC, 'WD');
BY LAST_NAME
IF WDAYS_HIRED NE 0
WHERE DEPARTMENT EQ 'PRODUCTION';
END
The output is:
LAST_NAME FIRST_NAME NEW_HIRE_DATE NEW_DAT_INC WDAYS_HIRED
--------- ---------- ------------- ----------- -----------
IRVING JOAN 1982/01/04 1982/05/14 94
MCKNIGHT ROGER 1982/02/02 1982/05/14 73
SMITH RICHARD 1982/01/04 1982/05/14 94
STEVENS ALFRED 1980/06/02 1982/01/01 414
ALFRED 1980/06/02 1981/01/01 153



*) examples are both copied from the manual.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
If it is only the difference in days you want, you can to it more simply than Frank's example and you don't need to use any subroutines. Converting a legacy date to a smartdate is simply an equality. Try this:

DEFINE FILE CAR
BEGDTI/I8YYMD=20070901;
ENDDTI/I8YYMD=20070915;
BEGDT/YYMD=BEGDTI;
ENDDT/YYMD=ENDDTI;
DIFFDAYS/I4=ENDDT-BEGDT;
END
TABLE FILE CAR
PRINT ENDDT BEGDT DIFFDAYS
BY COUNTRY
END


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
Ginny

Your solution is good, but it only calculates the difference in days (that's what Jason was asking for).
The formula I used gives you the opportunity to calc the number of days, months, years and business days. This was indeed not what Jason was asking for.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
I understand what you did, Frank. However, I believe in the KISS method (keep it simple, silly), especially for new users. Plus using core Focus is much more efficient than using subroutines.

At any rate, we were both right.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
It is easiest to use Standard Date formats, however, if you have legacy date formats such as I8YYMD then:

How to Calculate the Difference Between Two Dates

function(begin, end)

where:
function
Is one of the following:
DMY calculates the difference between two dates in day-month-year format.
MDY calculates the difference between two dates in month-day-year format.
YMD calculates the difference between two dates in year-month-day format.

begin
I, P, or A format with date display options.
Is the beginning date, or the name of a field that contains the date.

end
I, P, or A format with date display options.
Is the end date, or the name of a field that contains the date.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report 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     How to find day difference between to I8YYMD fields?

Copyright © 1996-2020 Information Builders