During nine years of blackguarding the J. D. Edwards date format (also known as JDate, also misnamed Julian Date),  I have resisted learning to write DB/2 stored procedures because the need to use them while developing OneWorld applications is minimal and OneWorld is the only context in which I have encountered DB/2.  Well, I have finally encountered that special confluence of  necessity and available time that produces a tool — in this case a function. I broke down, I learned, and I wrote that JDate to DB/2 Date function. Here is that code, in case you should find it useful:

-- We have to set the delimiter to ! so we can use
-- ; inside our function.  This is an intelligent comment
-- just for DB/2, but don't forget to tell your SQL tool to use !.
drop function myuser.JToDate!

CREATE function myuser.JToDate (JDEDate decimal(8,0))
    returns date
    language sql
    no external action
begin atomic
   if JDEDate = 0 then
      return null;
      return date(char(1900000 + Round(JDEDate, -3) + (JDEDate - Round(JDEDate, -3))));
   end if;

select JToDate(108098), JToDate(0), JToDate(imupmj)
from F4101!

I recommend the DB/2 SQL Cookbook as a pleasant mix of examples and syntax flows from which to learn.

– Adam

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>