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     [CODE] Handling JDEdwards dates

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CODE] Handling JDEdwards dates
 Login/Join
 
trob
Gold member
posted
This thread is a spawn off Get Week Number from Date in YYMD Format

Most of our data is coming from JDEdwards running on OS/400.
Prior to Y2K date fields were stored as a standard two-digit year julian date YYDDD. However the Y2K fix was to use CYYDDD. C is 0 for years before 2000 and 1 for 2000 through 2099, 2 for 2100 through 2199, and so on. Today June 19, 2009 is 109170.

Since I spend about 75% of my development time dealing with dates I quickly decided to create some functions to assist coding.

Here are several functions I created to handle JDEdwards dates. 
DEFINE FUNCTION JDETOSD(DATE_JDE/P7)
     DATE_GREG/I8YYMD = GREGDT(( 1900000 + DATE_JDE ), 'I8YYMD');
     JDETOSD/YYMD = DATECVT(DATE_GREG, 'I8YYMD', 'YYMD');
END

DEFINE FUNCTION JDETOID(DATE_JDE/P7)
     JDETOID/I8YYMD = GREGDT(( 1900000 + DATE_JDE ), 'I8YYMD');
END


DEFINE FUNCTION SDTOJDE(DATE_YYMD/YYMD)
     SDTOJDE/P7 = JULDAT(DATECVT(DATE_YYMD,'YYMD','I8YYMD'),'I5') + 100000;
END


DEFINE FUNCTION IDTOJDE(DATE_I8YYMD/I8YYMD)
     IDTOJDE/P7 = JULDAT(DATE_I8YYMD,'I5') + 100000;
END


I have placed these functions in edasprof.prf so now I can easily pass JDE date fields to these functions anywhere inside my code instead of having to re-code it. I also created reverse function to go the other way. (mainly used for where/if)

Ex.
COMPUTE INV_DATE1/YYMD = JDETOSD(SDIVD);
COMPUTE INV_DATE2/I8YYMD = JDETOID(SDIVD);
COMPUTE SDIVD2/P7 = SDTOJDE(INV_DATE1);
COMPUTE SDIVD3/P7 = IDTOJDE(INV_DATE2);

This message has been edited. Last edited by: Kerry,


------------------------------------------
DevStudio 8.2.03
WFS 8.2.03
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
losthiker
Member
posted Hide Post
I just started using Web Focus against JDE data. I am trying to develop a reporting object. How can you set a date range in a filter?


7.7.02, Windows Server 2008 R2
all output
 
Posts: 3 | Registered: July 15, 2011Report This Post
trob
Gold member
posted Hide Post
Yes. Here are two options. Let me know if this helps.

If you are prompting for the dates or setting them in the report you can either prompt and set from I8YYMD to JDE using the IDTOJDE function I listed.
-* &BEGIN/END DATES WILL BE IN P7 JDE Modified Julian format
-SET &BEGINDATE = IDTOJDE(&STARTDATE);
-SET &ENDDATE = IDTOJDE(&ENDINGDATE);

TABLE FILE BLA
PRINT
  BLA
  BLA
WHERE JDEFIELD GE &BEGINDATE
WHERE JDEFIELD LE &ENDDATE
END




Or define the JDE field and filter using YYMD format.

-* &BEGIN/END DATES WILL BE INPUTTED IN YYMD FORMAT AND JDEFIELD WILL BE DEFINED INTO YYMD FORMAT

DEFINE FILE BLA
  JDEFIELDYYMD/YYMD = JDETOSD(JDEFIELD);
END
 
TABLE FILE BLA
PRINT
  BLA
  BLA
WHERE JDEFIELDYYMD GE &BEGINDATE
WHERE JDEFIELDYYMD LE &ENDDATE
END


------------------------------------------
DevStudio 8.2.03
WFS 8.2.03
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
losthiker
Member
posted Hide Post
Thank you. Your wisdom helps!


7.7.02, Windows Server 2008 R2
all output
 
Posts: 3 | Registered: July 15, 2011Report This Post
Don Garland
Guru
posted Hide Post
Convert 20110928 to 111271

JDE Julian is made up of the following components
1 11 271
A BB CCC

A = Century (0=1900, 1=2000, 2=2100, etc)

BB = Year, as in 2011

CCC = Day of the year as in January 1, 2011 is day number 001 and December 25th is Day number 365

&YYMD is 20110928;

Example: -SET &TodayDateJul=JULDAT(&YYMD, 'I6') + 100000;

This message has been edited. Last edited by: Don Garland,
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report 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     [CODE] Handling JDEdwards dates

Copyright © 1996-2020 Information Builders