asked on

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

ASKER CERTIFIED SOLUTION

Barry Harper

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ShadowProgrammer

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.

Barry Harper

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

ShadowProgrammer

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.

ShadowProgrammer

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.