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     [CLOSED] Function DATEDIFF

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Function DATEDIFF
 Login/Join
 
roby
Silver Member
posted
Hi!
I am trying to get the difference between two dates using DATEDIFF but get wrong result.
Here is the code that I am using.

DEFINE FILE TOT
DTINIZ /YYMD = &DATAINI;
DTFINE /YYMD = &DATAFIN;
APPO_DIF_NEW /I4L= DATEDIF(DTINIZ,DTFINE,'BD');
END
-*
TABLE FILE TOT
PRINT APPO_DIF_NEW
ON TABLE HOLD AS 'LUNA'
END

When my begining date is 20090105 and end date is 20090106, I am getting the difference as 1.
The corect result is 0. Infact in Italy the 20090106 is not work.
How should I Know the calendar used?

Thanks for any help

This message has been edited. Last edited by: Kerry,
 
Posts: 31 | Location: roma | Registered: August 18, 2005Report This Post
GamP
Virtuoso
posted Hide Post
Roby,
The difference between the two dates you mention is very much 1.
But, I think you wanted not the absolute difference, but the difference in business days.
For that to accomplish, I refer to the documentation on this subject.
There is quite a lot of doc to be found on business days and how to implement it, and on top of that there is this little booklet, called '(Almost) 1001 Ways to Work with DATES in WebFOCUS'. This is very valuable literature for the WebFocus developer. It costs only $25, orderable through the IBI web site.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Jinx
Gold member
posted Hide Post
quote:
APPO_DIF_NEW /I4L= DATEDIF(DTINIZ,DTFINE,'BD');


Is this maybe a situation where you need a holiday table / file - so that public holidays are excluded from datediff type calculations. Something like SET HDAY = YOURDATES?


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Roby,

Look in the functions manual.
You will find:
quote:

Setting Business Days to Reflect Your Work Week.
The following designates work days as Sunday, Tuesday, Wednesday, Friday, and Saturday:
SET BUSDAYS = S_TW_FS


and for the holiday file:

quote:

Specifying Holidays
You can specify a list of dates that are designated as holidays in your company. These
dates are excluded when using functions that perform calculations based on working days.

To define a list of holidays, you must:
1. Create a holiday file using a standard text editor.
Dates must be in YYMD format.
Dates must be in ascending order.
Each date must be on its own line.
The file name must be HDAYxxxx.ERR, where xxxx can be any 4 characters.

2. Select the holiday file by issuing the SET command with the HDAY parameter.
SET HDAY = xxxx


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Jinx
Gold member
posted Hide Post
Sorry! Should really have given more info, just never sure if I am totally off the mark or not Razzer.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
jgelona
Master
posted Hide Post
Also, when setting up the holiday file, it has to be in the correct location and the documentation (at least in 7.1.3) was not very clear as to where that was. After I created the file and put it where the documentation said, it was not working. I had to call IBI to find the correct location for our installation.

Also, be sure and update it every year.

And one other thing to remember. When using DATEDIF with BD, if either date passed to DATEDIF is a non-business day (assuming that business days are MTWTF) the date is internally adjusted to the next business day. For example, assuming that Labor Day, Sep 7th, 2009 is in the holiday file:
DAYS1/I8=DATEDIF(20090907,20090903,'BD');
DAYS2/I8=DATEDIF(20090914,20090907,'BD');

DAYS1 will be 2 days and DAYS2 will be 4 days. In both computations, 20090907 is adjusted forward to 20090908.

The results will be the same if instead of 2009/09/07, one uses the 5th or 6th since those Sat & Sun. They are adjusted to the 8th, then the calculation is done.

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
Francis Mariani
Expert
posted Hide Post
quote:
the correct location
What is the correct location?


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
GinnyJakes
Expert
posted Hide Post
/WebFOCUS/ibi/srv76/wfs/bin


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
Francis Mariani
Expert
posted Hide Post
Ginny, thanks for the response. I was trying to make a point that if someone mentions there is a "correct location" perhaps they should mention where it is Smiler


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
jgelona
Master
posted Hide Post
Francis,

My point was that at the time I built my file the "correct location" was dependent on one's installation platform. That is Linux/Unix had one path and Windows another. I guess IBI has finalized that it will always be in the wfs/bin folder but my path is /opt/ibi/713/ibi/srv71/wfs/bin (don't ask me why, I didn't install the WebFOCUS Reporting Server). I went back and checked my old problem report and the pages on IBIs website that gave me the incorrect information are no longer available.

Also, just to add to the topic. All of the DATE functions, when using BD or WD will adjust dates that are not Business or Week days to the next Business or Week day before doing the function. So if one wants to add 3 business days to Sep 5, 2009, the result will be Sep 11,2009. Sep 5th will be adjusted to Sep 8th.


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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Function DATEDIFF

Copyright © 1996-2020 Information Builders