By using three SQL functions you can turn a JD Edwards Julian date into a
more usable format. The following SQL statement will give you all of the
sales order records from F4211 that have a transaction date greater than
today's date:
SELECT * FROM F4211
WHERE DATE(DIGITS(DECIMAL(SDTRDJ + 1900000,7,0))) > CURRENT DATE
The DECIMAL(SDTRDJ + 1900000,7,0) will turn the internal date into a Julian
format of YYYYNNN. The DIGITS function turns the numeric value into a
character string. The DATE function will convert the character string into a
date data type. The "CURRENT DATE" value will substitute today's date. To
specify a date other than today use:
SELECT * FROM F4211
WHERE DATE(DIGITS(DECIMAL(SDTRDJ + 1900000,7,0))) > '11/13/03'
At my current client site I used the following to create a function called
JDDCONV in library QGPL:
CREATE FUNCTION QGPL/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
I then can change my original SQL statement to:
SELECT * FROM F4211
WHERE JDDCONV(SDTRDJ) > CURRENT DATE
I hope this helps.
Mike Iaconis, President
Soar Technology Solutions
330-722-8106
[email protected]
