• 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
    Raz-Lee Security

    iSecurity Multi Factor Authentication (MFA) helps organizations meet compliance standards and improve the existing security environment on IBM i. It requires a user to verify his identity with two or more credentials.

    Key Features:

    • iSecurity provides Multi Factor Authentication as part of the user’s initial program
    • Works with every Authenticator App available in the Market.

    Contact us at https://www.razlee.com/isecurity-multi-factor-authentication/

    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

    • 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