wrkqry Selecting records based on current date

Posted on 2006-05-26
Last Modified: 2008-01-09
How do I Select records on based on the variable of todays date compared to going back X amount of days,months, or years. I know it has something to do with creating a field and expression but the records are in jullian date, and the today function returns a MMDDYY, there must be some generic way to bring a julian representation of the current date and subtract it to the desired range. Thanks

PS. This is too confusing:
Question by:royaltech
    LVL 13

    Accepted Solution

    If you are looking for a simple way,

    1) change your job date format to julian using CHGJOB DATFMT(*JUL)
    Now CURRENT(DATE) will return the current date in julian format

    2) define the result fields in your query, one to retrieve the current date:
    YYDDDDATE   current(date)
    and one to subtract an amount of years/months/days
    PREVDATE    YYDDDDATE - 3 years - 2 months - 1 days              
    where PREVDATE will be in julian format

    3) select the records by comparing to the date YOURDATE in the file:

    4) change your job date back to the default date format

    The query output for today looks like:
     06/146         03/084  

    Post back any questions!
    LVL 9

    Expert Comment

    As an alternative to changing your job date....
    you could convert the date in the file from Julian to a date...
    CHKDATE      date(substr(                    
    Calculate your comparison date based on CURRENT(DATE) adjusted by year, month and day

    CMPDATE      CURRENT(DATE) - 2 month  +2 day  
                              - 1 year          

    and then select records based on the comparison between CHKDATE and CMPDATE  eg.
    CHKDATE        LT      CMPDATE      
    CHKDATE        GE      CMPDATE

    This way, if you print dates, they are in a format which is more user friendly....
    hint : include the CMPDATE field in your selected fields so that you have an audit of what date was being used in the selection.


    LVL 13

    Expert Comment

    I like Tony's solution much better than my own.
    One snag that I couldn't figure out a way around: what if the dates are before and after the turn of the century?

    For example,
    I defined two result fields:
    CHKDATE1    date(substr(digits(yydddn+1900000),2,7))
    CHKDATE2    date(substr(digits(yydddn+2000000),2,7))

    and get the following query output for dates straddling 2000:
     6,082     ++++++++  03/23/06
    98,123     05/03/98    ++++++++

    Am I doing something wrong?
    LVL 9

    Expert Comment


    not sure why you are adding 2000000 to the date ???

    I am taking a 7 digit julian date as defined by IBM where the first digit is a century indicator 0 to signify a 1900's date and 1 to signify a 2,000 date; so I add 1900000 to get the correct 4 digit year
    005001  + 1900000 = 1905001 = 1st Jan 1905
    105001  + 1900000 = 2005001 = 1st Jan 2005

    LVL 9

    Expert Comment

    PS. Barry thanks for liking my formula better - even when you didn't think it catered for the turn of the century...

    Sorry - I should have said a 6 digit julian date not 7.....  

    AND even more sorry... as I think my formula might not work for royaltech !!!!!!

    I have just checked and I am not sure if IBM support the century indicator in a julian date !! They brought out a *LONGJUL date which is CCYYddd and *JUL is YYddd    (>40 = 1900 and <40 = 2000 covering 1940-2039).

    Where I currently work, they use JDEdwards and they have Julian Dates with a century indicator but I am not so sure that IBM are doing this OR JDEdwards !!!!

    I'll keep on looking.... and thinking though !!



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    Title # Comments Views Activity
    Start QSH command from CL 19 898
    Restrict FTP & ODBC Access 1 215
    AS/400 Backup to Disk 7 259
    Create AS400 Logical File 5 49
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now