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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Date Difference
 Login/Join
 
moyer1dl
Platinum Member
posted
I have a 7 digit alphanumeric date (ex. 2040505).

I want to subtract this date from the current date.

I am having trouble with the datedif function, I cant seem to get the right syntax on this.


We are using WF7.1.1 with VSAM files.

Any

Help

Dan


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
14 November, 2006:

20061114, 14112006, 11142006 is 8.
061114 , 141106 , 111406 is 6.

What's a 7 digit alphanumeric date? Do you mean a Julian Date?

I think you would need to convert to Gregorian before doing anything further with the date:

COMPUTE GREG_DATE/I8 = GREGDT(JULIAN, 'I8');


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
moyer1dl
Platinum Member
posted Hide Post
From my understanding it was a y2k issue

204 year 05month 05day this how you would read it.


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
As far as I can recall (it was so long ago!) the Y2K issue was that the century was missing, hence a 6 digit date. If you need to insert a zero to make it an 8 digit date, you could do this:

COMPUTE DATE2/A8YYMD =EDIT(DATE1,'90999999');

An example that might work for you:
TABLE FILE CAR
PRINT 
COUNTRY
COMPUTE TODAY/YYMD= '&YYMD';
COMPUTE DATE1/A7 = '2040505';
COMPUTE DATE2/A8YYMD =EDIT(DATE1,'90999999');
COMPUTE DATE3/YYMD = DATE2;
COMPUTE DIFF/I4 = DATEDIF(DATE3, TODAY, 'D');
END


DATE1 is your date. The D in the DATEDIF function provides the resulting difference in days. It could be Y for years or M for months.


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
Francis Mariani
Expert
posted Hide Post
From the docs:
quote:
Syntax: How to Find the Difference Between Two Dates
DATEDIF(from_date, to_date, 'unit'[, outfield])where:

from_date

Date


Is the start date from which to calculate the difference.


to_date

Date


Is the end date from which to calculate the difference.


unit

Alphanumeric


Is one of the following enclosed in single quotation marks:


Y indicates a year unit.


M indicates a month unit.


D indicates a day unit.


WD indicates a weekday unit.


BD indicates a business day unit.


outfield

Alphanumeric


Is the field that contains the result. This value is required only for Maintain.


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
oceannative
Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
14 November, 2006:

20061114, 14112006, 11142006 is 8.
061114 , 141106 , 111406 is 6.

What's a 7 digit alphanumeric date? Do you mean a Julian Date?

I think you would need to convert to Gregorian before doing anything further with the date:

COMPUTE GREG_DATE/I8 = GREGDT(JULIAN, 'I8');


I have an alphanumeric field that contains a Julian date. I want to convert it to a Gregorian date field. I used this compute, but it does not appear to do the conversion correctly... heres my compute: COMPUTE C_CLDATE2/I8 = GREGDT(QAF0401.QAF0401.A6URAB,'I8');

Before the compute: 106334
After the compute: 1061130

What am I missing?


Prod: WebFOCUS 7.1.6 - on Win 2K3/Tomcat - MRE/BID/RCaster/VisDis and AS400 (JDEWorld)
 
Posts: 23 | Location: North Carolina | Registered: May 16, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
TABLE FILE CAR
PRINT
COMPUTE JULDT/I7 = 2001203;
COMPUTE GREG_DATE/I8 = GREGDT(JULDT, 'I8');
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
END


Will give you the correct conversion when the Julian date you're trying to convert is correct.

The GREGDT function operates with Julian dates (two or four digit year and the number of days within that year, ie. 2001203 is 2001/07/22).

The examples you've provided are not valid Julian dates, eg. 2040505 - more than 366 days within year 2040, 106334 - not seven digits.

Check this out:
http://whatis.techtarget.com/definition/0,,sid9_gci212429,00.html

Particularly, "Commonly in computer programming, Julian date has been corrupted to mean the number of elapsed days since the beginning of a particular year. For example, in this usage, the Julian date for the calendar date of 1998-02-28 would be day 59."

Perhaps your dates are stored in some other format?


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
oceannative
Member
posted Hide Post
Yes, I did more research and found that the JDE version of the Julian date is 1st digit=century, 2nd/3rd=year, and 4-6 is the day #. As a workaround, I was able to join to a date logical so I can bring in the field as MDY, so now I'm researching to figure out how to make that a date field. When I change the format, it doesn't work, so I'm thinking I need to convert it to a DATE first. Thank you for the feedback...


Prod: WebFOCUS 7.1.6 - on Win 2K3/Tomcat - MRE/BID/RCaster/VisDis and AS400 (JDEWorld)
 
Posts: 23 | Location: North Carolina | Registered: May 16, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
quote:
1st digit=century, 2nd/3rd=year, and 4-6 is the day #

If you now know the format of the alpha field, you can insert the missing numeral to create a new alpha field and then convert that into a date field.

As per a previous example:
COMPUTE DATE1/A7 = '2040505';
COMPUTE DATE2/A8YYMD =EDIT(DATE1,'90999999');

But I'm still not sure how your second example, 106334, fits into the rule.

Cheers,


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
moyer1dl
Platinum Member
posted Hide Post
Thanks Francis your suggestion fixed my problem

Dan


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
 
Posts: 147 | Registered: June 24, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Thank you for your belated thank you 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
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders