Focal Point
Converting From JD Edwards Century Julian to Gregorian
January 19, 2010, 10:41 AM
CraigConverting From JD Edwards Century Julian to Gregorian
Does anyone have an expression that they use in DMC that will help me convert from a JD Edwards Century Julian date format (Example: 110001 for January 1, 2010) to any Gregorian date format? The GREGDT(indate, output_format) expression doesn't work in this circumstance. I know how to do it in Excel, but that doesn't help me here. Thanks in advance.
7.6.9, Linux
All
January 19, 2010, 11:11 AM
Tom FlynnHere is a very basic way:
-SET &ECHO=ALL;
-SET &XDATE = '110001';
DEFINE FILE CAR
DATE_IN/A6 = '&XDATE.EVAL';
DATE_1/A7 = IF EDIT(DATE_IN,'9') EQ '0' THEN '19' | EDIT(DATE_IN,'$99999') ELSE '20' | EDIT(DATE_IN,'$99999');
DATE_2/I7 = EDIT(DATE_1);
DATE_3/I8 = GREGDT(DATE_2,'I8');
END
TABLE FILE CAR
PRINT *
DATE_1 DATE_2 DATE_3
END
-EXIT
You can also set DEFCENT and YRTHRESH on the reporting server/in the program and do it that way...
hth
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
January 19, 2010, 01:52 PM
CraigTom,
Thanks for your reply. Where do I set the DEFCENT and YRTHRESH values? I can't seem to find them anywhere.
7.6.9, Linux
All
January 19, 2010, 02:07 PM
Tom FlynnHi Craig,
Actually, that wasn't correct advice to use DEFCENT and YRTHRESH; my bad!
GREGDT requires 5 or 7 digit Julian values with the actual year, i.e. 10001 or 2010001. Your example means the 1st byte is to be decoded into the century, so, DEFCENT and YRTHRESH wouldn't apply.
But, you can put these DEFINEs in the Master instead of multiple programs, or, have them in a common program and
-INCLUDE program_name...
Here is some documentation...
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
January 20, 2010, 08:54 AM
PBrightwellCraig,
I don't have to do that. When you generated the masters did you run them through the JDE conversion?
Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
January 21, 2010, 07:42 AM
trobFor Webfocus reporting I just added these functions to our edasprof. I use them all the time.
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
------------------------------------------
DevStudio 8.2.03
WFS 8.2.03