wrkqry Selecting records based on current date

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:

Who is Participating?
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!
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      

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.


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?

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

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 !!


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.