• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Convert J.D. Edwards Dates to Other Formats

    September 6, 2002 Timothy Prickett Morgan

    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
    
    R# D CONVDATE
    1 99001 01/01/1999
    2 100001 01/01/2000
    3 101001 01/01/2001
    4 99034 02/03/1999
    5 100066 03/06/2000
    6 101098 04/08/2001
    7 101365 12/31/2001

    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:

    R# D CONVDATE DAYOFWEEK QUARTER DMINUS31 MONTH ELAPSED_DAYS
    1 99001 01/01/1999 6 1 12/01/1998 1 1330
    2 100001 01/01/2000 7 1 12/01/1999 1 965
    3 101001 01/01/2001 2 1 12/01/2000 1 599
    4 99034 02/03/1999 4 1 01/03/1999 2 1297
    5 100066 03/06/2000 2 1 02/04/2000 3 900
    6 101098 04/08/2001 1 2 03/08/2001 4 502
    7 101365 12/31/2001 2 4 11/30/2001 12 235

    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 .

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: mgo_rc, Volume 2, Number 68 -- September 6, 2002

    Sponsored by
    DRV Technologies, Inc.

    Get More from Your IBM i

    Many users today struggle to get at the data they need on the IBM i. When users get reports, they look like they were formatted some time last century.

    Some organizations are still printing pre-printed forms and checks on impact printers.

    How often do operators log on to their system to look for messages they hope they don’t find?

    All of these scenarios can affect users’ perception of the IBM platform negatively, but there are simple solutions.

    DRV Technologies Inc. develops innovative solutions that help customers get more from their IBM i systems.

    Solutions include:

    • SpoolFlex spool conversion & distribution
    • FormFlex electronic forms
    • SecureChex MICR laser check printing
    • MessageFlex system monitoring

    FlexTools streamline resources, improve efficiency and enable pro-active system management.

    Better software, better service, DRV Tech.

    Learn how you can get more from your IBM i at www.drvtech.com

    Call 866 378-3366 for a Free Demonstration

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Parameterized RUNSQLSTM with Two or More Substitutions Reader Feedback and Insights: Security Flaws

    Leave a Reply Cancel reply

MGO Volume: 2 Issue: 68

This Issue Sponsored By

    Table of Contents

    • Reader Feedback and Insights: Copying Records in RPG
    • Convert J.D. Edwards Dates to Other Formats
    • Edit with Parentheses in Query/400, Take Two

    Content archive

    • The Four Hundred
    • Four Hundred Stuff
    • Four Hundred Guru

    Recent Posts

    • IBM Tweaks Some Power Systems Prices Down, Others Up
    • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
    • The Disconnect In Modernization Planning And Execution
    • Superior Support: One Of The Reasons You Pay The Power Systems Premium
    • IBM i PTF Guide, Volume 25, Number 13
    • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
    • When You Need Us, We Are Ready To Do Grunt Work
    • Generative AI: Coming to an ERP Near You
    • Four Hundred Monitor, March 22
    • IBM i PTF Guide, Volume 25, Number 12

    Subscribe

    To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

    Pages

    • About Us
    • Contact
    • Contributors
    • Four Hundred Monitor
    • IBM i PTF Guide
    • Media Kit
    • Subscribe

    Search

    Copyright © 2023 IT Jungle