Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Date/Time Calculation Subtraction Issue
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Date/Time Calculation Subtraction Issue
 Login/Join
 
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, 2017Reply With QuoteReport This Post
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: 2282 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6212 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Ditto to MartinY and Waz... Also verify the formats. Can't -108 be valid?
 
Posts: 3052 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
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: 1752 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
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: 2282 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 1655 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
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: 2282 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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, 2013Reply With QuoteReport This Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6212 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
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, 2017Reply With QuoteReport This Post
Expert
posted Hide Post


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6212 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Date/Time Calculation Subtraction Issue

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.