• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5295
  • Last Modified:

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:

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/q400/rbaf9mst02.htm
0
royaltech
Asked:
royaltech
  • 3
  • 2
1 Solution
 
_b_hCommented:
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:
YOURDATE    LT      PREVDATE

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

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

Post back any questions!
Barry
0
 
ShadowProgrammerCommented:
As an alternative to changing your job date....
you could convert the date in the file from Julian to a date...
CHKDATE      date(substr(                    
                  digits(YourJulianDate+1900000),2,7))    
                                             
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      
or
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.

Tony.

0
 
_b_hCommented:
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:
YYDDDN   CHKDATE1  CHKDATE2
 6,082     ++++++++  03/23/06
98,123     05/03/98    ++++++++

Am I doing something wrong?
Barry
0
 
ShadowProgrammerCommented:
Barry,

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
eg.
005001  + 1900000 = 1905001 = 1st Jan 1905
105001  + 1900000 = 2005001 = 1st Jan 2005

Tony.
0
 
ShadowProgrammerCommented:
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 !!


Tony.

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now