*JDE Julian Date conversion

["Lehti, Eric" <elehti@xxxxxxxxxxxx>]

I have not yet tried Alan Campin's iDate utility having documentation which states it can handle *JDEJUL  - CYYDDD Julian. Century Number (0 or 1) for J.D. Edward folks.



Reminds me of the *JDEJUL conversion offered by Howard Arner at http://www.sqlthing.com/Resources/JDDCONV.html

Howard lists the steps to  create a user defined function that encapsulates this logic to display *JDE dates in mm/dd/yy format:

select DATE(DIGITS(DECIMAL(ABUPMJ +1900000,7,0))), ABUPMJ from F0101



That web page states: the code for the UDF.



CREATE FUNCTION MCEIS.JDDCONV

(JDEDATE DECIMAL(6,0))

RETURNS DATE

LANGUAGE SQL

SET OPTION DATFMT=*ISO

BEGIN

DECLARE F_OUTPUT DATE ;

DECLARE F_TEST INTEGER ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET F_TEST = 1 ;

SET F_TEST = 0;

SET F_OUTPUT = DATE(DIGITS(DECIMAL(JDEDATE+1900000,7,0)));

IF F_TEST = 0 THEN RETURN F_OUTPUT ;

ELSE RETURN NULL;

END IF;

END