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] Date/Time Calculation Subtraction Issue

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Date/Time Calculation Subtraction Issue
 Login/Join
 
NVLee
Member
posted
Hello,

I'm currently modifying an old report that we have in our library and was wondering if this line of code is acceptable for defining a code for subtracting expiration dates from the current date.
The following code is used in our DEFINE portion:

  

CURRENT_DATE/YYMD = '&YYMD';
DLEXPDATE/YYMD = DLEXPDATE2;
DL_DAYS_TO_EXP/I5  =  IF DLEXPDATE NE 0 THEN (DLEXPDATE - CURRENT_DATE) ELSE 9999;


We then use that line of code in a WHERE statement to filter out any employee that has an expiration date less than or equal to 30, 60, or 90 days. 

 

WHERE TOTAL DL_DAYS_TO_EXP LE &DAYS.(<30 Days,30>,<60 Days,60>,<90 Days,90>).Expires Within.;


The issue is that one of the results is returning '-108' days while the others are returning the correct, positive numbers.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8.1, Developer Studio 8.105, App Studio 8.105, InfoAssist
 
Posts: 4 | Location: Metro Detroit | Registered: March 08, 2017Report This Post
MartinY
Virtuoso
posted Hide Post
You should better use the DATEDIF() function to calculate days between dates.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Waz
Expert
posted Hide Post
I would check your data to see what values are in DLEXPDATE.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Doug
Expert
posted Hide Post
Ditto to MartinY and Waz... Also verify the formats. Can't -108 be valid?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Hi NVLee

Welcome to Focal Point! It is a great forum for getting quick answers to your development questions...

I agree with Martin that DATEDIF is the way to go..


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
MartinY
Virtuoso
posted Hide Post
quote:
The issue is that one of the results is returning '-108' days while the others are returning the correct, positive numbers


What you have to understand is that by doing : DLEXPDATE - CURRENT_DATE you are performing a numerical subtraction such as 15 - 8 which is good while both of your arguments are in the same month (and in the proper order in the equation) otherwise this is the reason why you have negative value. And much more true when your ExpiryDate (DLEXPDATE) is before the current date.

Sample :

20171120 - 20171108 = 12

20171010 - 20171108 = -98 (should be 29 days, not -98)


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Wep5622
Virtuoso
posted Hide Post
quote:
Originally posted by Doug:
Ditto to MartinY and Waz... Also verify the formats. Can't -108 be valid?


Indeed. I would think that -108 is a result that is already 108 days past its expiration date. That could very well be a valid result.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
MartinY
Virtuoso
posted Hide Post
quote:
I would think that -108 is a result that is already 108 days past its expiration date.


Could be, but if you look at my previous sample calculation, I doubt that -108 is really 108 days ago...


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
eric.woerle
Master
posted Hide Post
You might need to check for null values in your data as well. Null's do wierd things


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Waz
Expert
posted Hide Post
quote:
Null's do wierd things


Ditto to that


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
NVLee
Member
posted Hide Post
Thank you for your help everyone! I've been playing around with the DATEDIF() function as suggested. Based on new information I received recently I am now awaiting if the data in our system is actually correct to begin with since it returned a -108 in our AS400 system as well.


WebFOCUS 8.1, Developer Studio 8.105, App Studio 8.105, InfoAssist
 
Posts: 4 | Location: Metro Detroit | Registered: March 08, 2017Report This Post
Waz
Expert
posted Hide Post


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 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     [SOLVED] Date/Time Calculation Subtraction Issue

Copyright © 1996-2020 Information Builders