DB2 JDE Date Helpers

dates.sql

-- Getting Gregorian Date from Julian Column:
CASE WHEN [JULIAN] IS NULL OR [JULIAN] = 0 THEN '' ELSE CAST(CAST(CAST(((CAST(([JULIAN]-MOD([JULIAN],1000))/1000 AS INT)+1900)*1000)+MOD([JULIAN],1000) AS CHAR(7)) AS DATE) AS CHAR(10)) END
-- Getting Time from 6-digit time (130045 = 1:00:45pm)
CAST(SUBSTR([TIMESTR],1,2) || ':' || SUBSTR([TIMESTR],3,2) || ':' || SUBSTR([TIMESTR],5,2) as TIME)

-- Current Date in JDE Julian Format:
select (YEAR(NOW()) - 1900) || LPAD(DAYOFYEAR(NOW()),3,'0') from sysibm.sysdummy1;
--or
select (100000+(YEAR(curdate())-2000)*1000+dayofyear(curdate())) from sysibm.sysdummy1;

-- Current Time in JDE Julian Format:
select VARCHAR_FORMAT(NOW(),'HH24MISS') from sysibm.sysdummy1;
--or
select replace(CHAR(curtime()),'.','') from sysibm.sysdummy1;
--or
select INT(CURRENT TIME) from sysibm.sysdummy1 -- (no left padding)