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     [CODE] Handling JDEdwards dates
Go
New
Search
Notify
Tools
Reply
  
[CODE] Handling JDEdwards dates
 Login/Join
 
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, 2007Reply With QuoteReport This Post
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, 2011Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thank you. Your wisdom helps!


7.7.02, Windows Server 2008 R2
all output
 
Posts: 3 | Registered: July 15, 2011Reply With QuoteReport This Post
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: 290 | Location: Greater Cincinnati  | Registered: May 11, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CODE] Handling JDEdwards dates

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