• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Going from a True Date to a JDE Date

    November 6, 2002 Timothy Prickett Morgan

    Hey, Howard:

    The function you wrote in the Midrange Guru article, “JD Edwards Dates, Revisited ,” does a great job of converting a JDE date to a ‘true’ date. But what about converting a true date to a JDE date? Suppose I have a true date, for example the ‘current date’ and I want to convert that to a JDE date?

    — Andy

    Wow, I did not even think of the reverse, and now that you brought it up I see what a gaping hole that is. I can image a lot of situations where you need to go from ‘real’ dates to JDE dates, so let’s get cracking on a formula to do that!

    For review, JDE dates are stored in a decimal(6,0) field in the format CYYDDD, where C is 0 for years before 2000 and 1 for years of 2000 or after, YY represents the two-digit year, and DDD is the day number from January 1. So, representing 01/01/2002 in JDE is 102001.

    Now, I have written two previous articles on how to convert JDE to real dates. The first article, “Convert J.D. Edwards Dates to Other Formats,” provides the formula to use in an SQL statement, whereas the second article, which you mentioned in your question , shows how to do it with a user-defined function. Review those articles to learn how to convert from JDE to a date.

    Before we attempt the formula, let’s create a table and add some sample data:

    CREATE TABLE MCEIS.T1
       (D DECIMAL(6,0), RD DATE);
    
    INSERT INTO MCEIS.T1 VALUES (99001,'01/01/1999');
    INSERT INTO MCEIS.T1 VALUES (100001,'01/01/2000');
    INSERT INTO MCEIS.T1 VALUES (101001,'01/01/2001');
    INSERT INTO MCEIS.T1 VALUES (99034,'02/03/1999');
    INSERT INTO MCEIS.T1 VALUES (100066,'03/06/2000');
    INSERT INTO MCEIS.T1 VALUES (101098,'04/08/2001');
    INSERT INTO MCEIS.T1 VALUES (101365,'12/31/2001');
    

    The above statements create a table called T1 in the library MCEIS that contains two fields: D that is meant to hold JDE dates and RD that holds the corresponding real date. The next statements add records to the tables that show the JDE date and the corresponding real date. Once you have the sample data, it’s time to move to the function.

    An Ugly Way

    I’m going to show you two ways to convert the dates. The second one is more elegant, but I want you to see the first method because it illustrates some interesting side effects of SQL math.

    First, let’s worry about how to extract the century flag. The YEAR SQL function can be used to return the year portion of a date as an integer. So the following portion of the formula will yield 0 for years prior to 2000 and 1 for years 2000 and after.

    YEAR(RD)/1000-1
    

    Ah, I bet you are thinking, “Howard, 1999/1000 is 1.999. Your formula won’t yield 0 for years less than two thousand!” Remember, the YEAR function returns an INTEGER data type, and since you are dividing by a whole number the AS/400 will use integer division, which truncates the result. This is one place where you need to understand how the AS/400 does math within SQL. In this case, the AS/400 is saving you a lot of time by not requiring you to cast the result of the division back to integer. Since both operands are integer, the result is integer.

    Next, we need to extract the last two digits of the year value.

    MOD(YEAR(RD),100)
    

    The above expression uses the MOD function to return mod 100 of the year portion of the date. If the date is 1999, this would return 99. If the date is 2001, this would return a number 1.

    Finally, we need to extract the days portion of the date in order to create our JDE date. The DAYOFYEAR function returns the day number of the year that the date represents. If the input date is 01/01/2003 then the result would be 1. If the input date is 02/03/1999 then the result would be 34.

    Now that you understand the elements of how I am going to manipulate the date, let’s take a look at the function in its entirety and see how I use other SQL functions to transform the individual parts into a string representation of the date.

    DIGITS(DECIMAL(YEAR(rd)/1000-1,1))||
    DIGITS(DECIMAL(MOD(YEAR(rd),100),2))||
    DIGITS(DECIMAL(DAYOFYEAR(rd),3)),6,0)
    

    The above expression uses the concatenation operator, ||, to concatenate the results of the three expressions into a character string representing the JDE date. The DECIMAL function is used around each conversion to ensure that the number is cast to an appropriate DECIMAL length so that the DIGITS function will operate correctly. DIGITS looks at the length of a decimal number and returns a zero-filled character string of that length. So, if your number is 1 and you cast to DECIMAL(3) and then ask for DIGITS, you would return the string ‘001,’ which is the zero-filled CHAR(3) representation of the decimal(3) number 1.

    If we do the above expression in a select statement against the test table, we will see the following results:

    SELECT d, rd,
          DIGITS(DECIMAL(YEAR(rd)/1000-1,1))||
          DIGITS(DECIMAL(MOD(YEAR(rd),100),2))||
          DIGITS(DECIMAL(DAYOFYEAR(rd),3))
          AS JDEDATE
    FROM mceis.t1;
    

    That result would yield this information:

    D RD JDEDATE
    99001 01/01/1999 99001
    100001 01/01/2000 100001
    101001 01/01/2001 101001
    99034 02/03/1999 99034
    100066 03/06/2000 100066
    101098 04/08/2001 101098
    101365 12/31/2001 101365

    We are almost there. We just need to convert the string into a DECIMAL(6,0) field so that we can use it in JDE. The following statement will return the RD column as a valid JD Edwards DECIMAL(6,0) date:

    SELECT d,rd,
    DECIMAL(DIGITS(DECIMAL(YEAR(rd)/1000-1,1))||
    DIGITS(DECIMAL(MOD(YEAR(rd),100),2))||
    DIGITS(DECIMAL(DAYOFYEAR(rd),3)),6,0)
    AS JDEDATE
    FROM mceis.t1;
    

    That will yield the following information:

    D RD JDEDATE
    99001 01/01/1999 99001
    100001 01/01/2000 100001
    101001 01/01/2001 101001
    99034 02/03/1999 99034
    100066 03/06/2000 100066
    101098 04/08/2001 101098
    101365 12/31/2001 101365

    A Better Formula!

    It has been said that within every large program, there is a small program struggling to get out. Maybe the same is true of SQL queries. Here is an easier formula than the first one I developed.

    (YEAR(RD)-1900)*1000+DAYOFYEAR(RD)
    

    Here is the SQL statement that will use the second formula and return the data as a DECIMAL(6,0):

    SELECT D, RD, 
    DECIMAL((YEAR(RD)-1900)*1000+DAYOFYEAR(RD),6) AS JDEDATE
    FROM MCEIS.T1;
    

    That yields the following information:

    D RD JDEDATE
    99001 01/01/1999 99001
    100001 01/01/2000 100001
    101001 01/01/2001 101001
    99034 02/03/1999 99034
    100066 03/06/2000 100066
    101098 04/08/2001 101098
    101365 12/31/2001 101365

    In an upcoming issue of Midrange Guru, I will write a user-defined function to perform this conversion. I will provide both the function code and a downloadable version of the function available from my Web site so that users with less than V5R1 or no access to the C compiler can use the function.

    — 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 go to www.sqlthing.com to find out more about manipulating dates on the AS/400.

    Sponsored By
    ADVANCED SYSTEMS CONCEPTS

    Business Analytics
    – Practical –
    – Cost Effective –
    – Easy to Deploy –

    SEQUEL FYI

    User Quote:
    “I love SEQUEL FYI because it lets me look at the data any way I need to see it, instantly. This is the easiest tool to manage complex product relationships that I have ever seen.”

    SEQUEL FYI offers outstanding OLAP business intelligence functionality for a fraction of the cost of comparable solutions.

    Read More > View Streaming Video

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: mgo_rc, Volume 2, Number 85 -- November 6, 2002

    Sponsored by
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions.

    Upgrading to Power10, Power11, or cloud hosted system, Maxava has you covered!

    Book A Consultation Today

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    You Can’t Get There from Here Reader Feedback and Insights: Overlooking the Obvious

    Leave a Reply Cancel reply

MGO Volume: 2 Issue: 85

This Issue Sponsored By

    Table of Contents

    • Reader Feedback and Insights: We Want More RPG
    • Going from a True Date to a JDE Date
    • Use the Full-Screen Debugger with OPM Programs

    Content archive

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

    Recent Posts

    • With Power11, Power Systems “Go To Eleven”
    • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
    • Izzi Buys CNX, Eyes Valence Port To System Z
    • IBM i Shops “Attacking” Security Concerns, Study Shows
    • IBM i PTF Guide, Volume 27, Number 26
    • Liam Allan Shares What’s Coming Next With Code For IBM i
    • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
    • VS Code Will Be The Heart Of The Modern IBM i Platform
    • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
    • IBM i PTF Guide, Volume 27, Number 25

    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 © 2025 IT Jungle