SQL function to convert JDE dates (CYYDDD)?

[Viking]

Hi, does anyone have an SQL function they'd like to share that converts JD Edwards dates (CYYDDD) to dates, or YYMMDD, or some format that is more useful than JDE dates?

I'd like to have an SQL function so that I can do something like Exec SQL SELECT JDE2DATE(JDEDATE) FROM MYFILE. I'm not very experienced with creating SQL functions (although I did create a couple a few years ago as I recall), so I appreciate any help.

Thanks.

[B.Hauser]

Re: SQL function to convert JDE dates (CYYDDD)?

Try:

Select Date(VarChar(JDEDATE + 1900000)) ...
   From File
   ...

If you need an UDF just use the wizzard in IBM i Navigator.

CREATE FUNCTION YourSchema.YourUDF ( PARCYYDDD DECIMAL (6, 0) ) 
       RETURNS  DATE  
       LANGUAGE SQL 
       MODIFIES SQL DATA 
Return Date(VarChar(ParCYYDDD + 1900000));

Birgitta

[Viking]

Re: SQL function to convert JDE dates (CYYDDD)?

Vielen Dank Birgitta! Works great.

[Rocky]

Re: SQL function to convert JDE dates (CYYDDD)?

Hi, does anyone have an SQL function they'd like to share that converts JD Edwards dates (CYYDDD) to dates, or YYMMDD, or some format that is more useful than JDE dates?

I'd like to have an SQL function so that I can do something like Exec SQL SELECT JDE2DATE(JDEDATE) FROM MYFILE. I'm not very experienced with creating SQL functions (although I did create a couple a few years ago as I recall), so I appreciate any help.

Thanks.

Yes - I created a couple of SQL functions...

/* Creating function DATE2JDE */
CREATE or replace FUNCTION QGPL.DATE2JDE( DTE DATE ) RETURNS NUMERIC (6, 0)
LANGUAGE SQL
CONTAINS SQL
SPECIFIC QGPL.DATE2JDE_1
CONCURRENT ACCESS
RESOLUTION DEFAULT
FENCED
DETERMINISTIC
CALLED ON NULL INPUT
NO EXTERNAL ACTION
SET OPTION DBGVIEW = *SOURCE
begin
return ((year(dte) - 1900) * 1000) + dayofyear(dte);
end;;


/* Setting label text for DATE2JDE */
LABEL ON SPECIFIC ROUTINE QGPL.DATE2JDE_1 IS 'Date to JDE format';


/* Setting comment text for DATE2JDE */
COMMENT ON PARAMETER SPECIFIC ROUTINE QGPL.DATE2JDE_1
           (DTE IS 'Date field' ) ;;


/* Creating function DATE2JDE */
CREATE or replace FUNCTION QGPL.DATE2JDE( DTE VARCHAR(10))
            RETURNS  NUMERIC (6, 0)
LANGUAGE SQL
CONTAINS SQL
SPECIFIC QGPL.DATE2JDE_2
CONCURRENT ACCESS
RESOLUTION DEFAULT
FENCED
DETERMINISTIC
CALLED ON NULL INPUT
NO EXTERNAL ACTION
SET OPTION DBGVIEW = *SOURCE
begin
DECLARE WORKDATE DATE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN NULL;
SET WORKDATE = DATE(DTE);
return ((year(WORKDATE) - 1900) * 1000) + dayofyear(WORKDATE);
end;;


/* Setting label text for DATE2JDE */
LABEL ON SPECIFIC ROUTINE QGPL.DATE2JDE_2 IS 'Date to JDE format';


/* Creating function DATE2JDE */
CREATE or replace FUNCTION QGPL.DATE2JDE( DTE NUMERIC(8,0),FMT VARCHAR(8))
       RETURNS  NUMERIC (6, 0)
LANGUAGE SQL
CONTAINS SQL
SPECIFIC QGPL.DATE2JDE_3
CONCURRENT ACCESS
RESOLUTION DEFAULT
FENCED
DETERMINISTIC
CALLED ON NULL INPUT
NO EXTERNAL ACTION
SET OPTION DBGVIEW = *SOURCE
begin
DECLARE STRWORKDATE CHAR(8);
DECLARE WORKDATE DATE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN NULL;
SET STRWORKDATE = TRIM(CHAR(DTE));
IF FMT='YYYYMMDD' THEN
SET WORKDATE = DATE(SUBSTRING(STRWORKDATE,1,4) CONCAT '-' CONCAT
   SUBSTRING(STRWORKDATE,5,2) CONCAT '-' CONCAT SUBSTRING(STRWORKDATE,7,2));
ELSE
set strworkdate = right(trim('00' concat strworkdate),8);
SET WORKDATE = DATE(SUBSTRING(STRWORKDATE,5,4) CONCAT '-' CONCAT
   SUBSTRING(STRWORKDATE,1,2) CONCAT '-' CONCAT SUBSTRING(STRWORKDATE,3,2));
END IF;
return ((year(WORKDATE) - 1900) * 1000) + dayofyear(WORKDATE);
end;;


/* Setting label text for DATE2JDE */
LABEL ON SPECIFIC ROUTINE QGPL.DATE2JDE_3 IS 'Date to JDE format';


/* Setting comment text for DATE2JDE */
COMMENT ON PARAMETER SPECIFIC ROUTINE QGPL.DATE2JDE_2
       (DTE IS 'Char field' );


/* Creating function JDE2DATE */
CREATE or replace FUNCTION QGPL.JDE2DATE( JDEDATE NUMERIC(6))
RETURNS  DATE
LANGUAGE SQL
SPECIFIC QGPL.JDE2DATE_1
CONTAINS SQL
CONCURRENT ACCESS
RESOLUTION DEFAULT
FENCED
DETERMINISTIC
CALLED ON NULL INPUT
NO EXTERNAL ACTION
SET OPTION DBGVIEW = *SOURCE,DATFMT=*ISO
begin
DECLARE DATEFLD DATE;
DECLARE INVALID_DATE CONDITION FOR SQLSTATE '22007';
DECLARE EXIT HANDLER FOR INVALID_DATE
     RETURN  NULL;
SET DATEFLD = DATE(char(1900000 + JDEDATE));
return DATEFLD;
end;;
/* Setting label text for JDE2DATE */
LABEL ON SPECIFIC ROUTINE QGPL.JDE2DATE_1 IS 'Date to JDE format';;


/* Setting comment text for JDE2DATE */
COMMENT ON PARAMETER SPECIFIC ROUTINE QGPL.JDE2DATE_1
    (JDEDATE IS 'Date field' );

[Viking]

Re: SQL function to convert JDE dates (CYYDDD)?

Rocky, I just created the function JDE2DATE based on yours above and it works great - thanks!

[Rocky]

Re: SQL function to convert JDE dates (CYYDDD)?

You may want to create the others too as it takes the date and put's it in JDE format - whether you use date field, character or numeric field. If it's numeric it wants a format ('YYYYMMDD' means date is 20141111, otherwise it's going to assume MMDDYYYY.... 11112014).