DB2/400 SQL UDF (User-Defined-Function) for Julian date conversion

slandess

slandess

Well Known Member
*** This post pertains to DB2/400 SQL queries (i.e., System i - iSeries - AS/400) ***

Do you use IBM's STRSQL or Query Management to produce ad-hoc reports when viewing data in JDE files? If you don't have a Julian calendar at hand, JDE Julian dates can be annoying when running SQL queries against JDE files.

It can be equally annoying to join each date field in your query to the F00365 file to convert it to human-readable form.

I have created a SQL UDF which will turn those dates into an *ISO date to make those ad-hoc SQL queries easier!

See attachment.

Regards,
 

Attachments

  • 129610-JDEDATEP.doc
    41.5 KB · Views: 535
Here is my SQL UDF for Julian to Date String conversion

CREATE FUNCTION DBA.JULIANTODATE (
INJULIAN NUMERIC(18, 0) )
RETURNS CHAR(10)
LANGUAGE SQL
SPECIFIC DBA.JULIANTODATE
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
BEGIN
DECLARE STRDATE CHAR ( 10 );
DECLARE STRJULIAN VARCHAR ( 6 );
SET STRJULIAN = LTRIM ( RTRIM ( CHAR ( INJULIAN ) ) ) ;

IF LENGTH ( STRJULIAN ) = 5 THEN
SET STRDATE =CHAR ( DATE ( '01/01/19' || SUBSTRING ( STRJULIAN , 1 , 2 ) ) + ( INT ( SUBSTR ( STRJULIAN , 3 , 5 ) ) - 1 ) DAYS );
ELSEIF LENGTH ( STRJULIAN ) = 6 THEN
SET STRDATE = CHAR ( DATE ( '01/01/20' || SUBSTRING ( STRJULIAN , 2 , 2 ) ) + ( INT ( SUBSTR ( STRJULIAN , 4 , 6 ) ) - 1 ) DAYS );
ELSE
SET STRDATE = NULL ;
END IF ;
RETURN STRDATE ;
 
Back
Top