• 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
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    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

    • Fortra Issues 20th State of IBM i Security Report
    • FNTS Launches Managed Services for Power Servers in IBM Cloud
    • Total LTO Shipped Capacity Up Slightly in 2022
    • Four Hundred Monitor, May 24
    • Update On Critical Security Vulnerability In PowerVM
    • Critical Security Vulnerability In PowerVM Hypervisor
    • IBM Power: Hosted On-Premises Or In The Cloud?
    • Guru: Watch Out For This Pitfall When Working With Integer Columns
    • As I See It: Bob-the-Bot
    • IBM i PTF Guide, Volume 25, Number 21

    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