Convert J.D. Edwards Dates to Other Formats

Hey, Howard:

I want to convert a J.D. Edwards date, (CYYDDD where C represents the century, YY represents the year, and DDD represents the day of the year) to a more readable format, (like YYYY-MM-DD). I can do this in Crystal Reports, but is there a way to do it in SQL?

-- Domenic

This is a good one, Domenic, as I first thought it was impossible, but on further reading of the IBM documentation, I found a little known use for the DATE function. I found that DATE can take a variety of arguments. As I was reading the documentation on DATE, I noticed that one of the acceptable date formats is a seven-character string in the form YYYYNNN where YYYY is the year and NNN is the day number. When passed this information, DATE will return a date data type value representative of the string that was passed in.

To take advantage of this feature, we need only to convert your DECIMAL(6,0) representation of the date into the required CHAR(7) representation and pass it to the date function. Let's do it!

First we create some test data:

CREATE TABLE mceis.t1 (d DECIMAL(6,0))
INSERT INTO mceis.t1 VALUES (099001)
INSERT INTO mceis.t1 VALUES (100001)
INSERT INTO mceis.t1 VALUES (101001)
INSERT INTO mceis.t1 VALUES (099034)
INSERT INTO mceis.t1 VALUES (100066)
INSERT INTO mceis.t1 VALUES (101098)
INSERT INTO mceis.t1 VALUES (101198)
INSERT INTO mceis.t1 VALUES (101298)
INSERT INTO mceis.t1 VALUES (101365)

Notice that I used the SQL naming convention. The collection and table names are separated by a period (.). Be sure to use a forward slash (/) instead of a period if you're using SYS naming convention.

OK, now that we have some data, let's work on how to make the string we need. The first step is to convert the CYY representation of the year to a four-digit year:

DECIMAL(d +1900000,7,0))

The next step is to convert the seven-digit decimal number to a character string. The DIGITS function does this nicely:

DIGITS(DECIMAL(d +1900000,7,0)))

Here is an example statement using the transformation and the resulting data:

SELECT d, DATE(DIGITS( DECIMAL(d +1900000,7,0)) ) AS CONVDATE
 FROM mceis.t1

Now, let's use the formula in a common table expression and then use the resulting field in a number of expressions:

SELECT D, CONVDATE, 
   DAYOFWEEK(CONVDATE) AS DAYOFWEEK, 
   QUARTER(CONVDATE) AS QUARTER,
   CONVDATE - 31 days AS DMINUS31,
   MONTH(CONVDATE) AS MONTH,
   days(CURRENT_DATE)-days(CONVDATE) AS ELAPSED_DAYS
FROM (SELECT d, DATE(DIGITS( DECIMAL(d +1900000,7,0))) AS CONVDATE
        FROM mceis.t1) AS X

Notice that an SQL statement is nested within a FROM clause. The inner SQL statement runs first; yielding a temporary table I call X. The outer SELECT then runs against this temporary table.

The above statement would yield the following result table from the sample data:

So, using the date data type can open up a world of calculations against your data. I hope this helps.

-- Howard

Howard F. Arner, Jr. is a writer and consultant for Client Server Development, Inc . You can purchase Howard's book, "iSeries and AS/400 SQL at Work," from www.sqlthing.com/books or visit www.sqlthing.com to find out more about manipulating dates on the AS/400.

Sponsored By WORKSRIGHT SOFTWARE

On June 30, 2002, $$$$$$$$    Postal Rates went UP!    $$$$$$$$ On July 1, 2002, $$$$$    you wanted your postage bill to go down.    $$$$$ We have the solution! CASS certify your mailing names and addresses and presort your outgoing mail and save. Our CASS certification software ensures that your address files have valid ZIP Code and address information. Our presort software ensures that you can properly prepare you mail for delivery to your Post Office. WorksRight Software, Inc. is the number-one source for iSeries and AS/400 CASS, presort, ZIP Code, and area code software and data. Visit our Web site - www.worksright.com - to learn more about our CASS and presorting software, or contact WorksRight Software, Inc., phone 601-856-8337, e-mail software@worksright. com .