• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • OPNQRYF Date Arithmetic

    September 25, 2002 Timothy Prickett Morgan

    Hey, Ted:

    I need to select records for the period beginning 210 days ago and ending 30 days ago using Open Query File (OPNQRYF). The date field is eight digits packed, in YYYYMMDD format.

    I thought this was going to be easy, but I am a little tired and stumped.

    So how do I do this?

     

    — David

    You’ll have to convert that numeric date to a date data type in order to use OPNQRYF’s date duration functions.

    Here’s an example that illustrates the conversion:







    DCL        VAR(&QRYSLT) TYPE(*CHAR) LEN(512)
                                                             
    DCL        VAR(&QRYSLT) TYPE(*CHAR) LEN(512)
    
    CHGVAR     VAR(&QRYSLT) + 
                 VALUE('DtDate *ge (%curdate - %durday(210)) *and +
                        DtDate *le (%curdate - %durday(30))')
    OPNQRYF    FILE((GLJNLY)) QRYSLT(&QRYSLT) +
                 MAPFLD((DIGDATE '%DIGITS(jedate)' *CHAR 8) +
                        (EDDATE '%sst(digdate 5 2) *cat "/" +
                            *cat %sst(digdate 7 2) *cat "/" +
                            *cat %sst(digdate 3 2)' *CHAR 8) +
                        (DTDATE '%date(eddate)' *DATE))    
    CPYFRMQRYF FROMOPNID(GLJNLY) TOFILE(QTEMP/X) +
                 MBROPT(*REPLACE) CRTFILE(*YES)
    CLOF       OPNID(GLJNLY)
    RUNQRY     QRYFILE((QTEMP/X))
    

    The trick to this is in the three mapped fields. DIGDATE uses the %digits function to convert the numeric field to an eight-byte character field. EDDATE uses substringing and concatenation to reformat DIGDATE into the system format, which, on the Netshare/400 machine on which I created this example, is MM/DD/YY. The DTDATE field changes EDDATE to the date data type. The QRYSLT parameter selects records based on DTDATE, not the original field JEDATE.

    — Ted

    Sponsored By
    COMMON

    COMMON IT EXECUTIVE CONFERENCE

    Tell your IT Directors about the three-day event that is specially designed for them. Held October 13-15, 2002, in Denver, the IT Executive Conference will give iSeries Directors the opportunity to network and learn from well-known speakers in the iSeries environment, and receive new strategies for optimizing operations.

    To find out more, go to: http://www.common.org/executive

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: mgo_rc, Volume 2, Number 73 -- September 25, 2002

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    JD Edwards Dates, Revisited Reader Feedback and Insights:

    Leave a Reply Cancel reply

MGO Volume: 2 Issue: 73

This Issue Sponsored By

    Table of Contents

    • Reader Feedback and Insights: Displaying Menu Options
    • FTP’ing a Library
    • OPNQRYF Date Arithmetic

    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