iSeries query to pull records by date

I have a query I am trying to get to run in JDE.  I am trying to get a listing of all work orders which have been closed in the past 5 days.

Everything works until I try to get a date qualifier in there (WASTRX is our 'date closed' field).

Here is my query.  I'm comfortable with SQL, just not iSeries....

          STRIP(F4801.WARORN, LEADING, '0' ) = STRIP( DIGITS( F4211.SDDOCO), LEADING, '0' )
          AND F4801.WALNID = FLOOR( F4211.SDLNID )
     WASRST > '93'
     AND WARORN<> ' '

I WASTRX is stored as a 'normal' date (mm/dd/yyy).  I am assuming that CURDATE() is in Julian format.  I'm thinking I need to format one to match the other prior to comparison, but I'm not sure how to do that.

Thanks for any help you can provide!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
You say WASTRX is stored as a "normal" date, but if that were the case, I believe your query would work.

Is the data-type of WASTRX an actual DATE format or is it stored as some other type? (possibly an integer or a character type)

A simple DspFFD on that file should show you the actual data-type.

-- DaveSlash
dimmergeekAuthor Commented:
You lost me at:
     'A simple DspFFD on that file should show you the actual data-type.'

However, the field WASTRX is stored as a six-digit numeric data type.
Dave FordSoftware Developer / Database AdministratorCommented:
Excellent. Can you please show me a few examples of how the data looks for that column?

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

dimmergeekAuthor Commented:
I managed to get a DspFFD:

              Data        Field     Buffer    Buffer           Field       Column  
Field      Type       Length  Length   Position        Usage    Heading
WASTRX     ZONED        6  0       6       427        Both     WASTRX

Data from that column in a query:

select distinct(WASTRX) from e1proddta/f4801
Dave FordSoftware Developer / Database AdministratorCommented:
I hate Julian dates. The (incredibly small) space-savings they offer is far outweighed by the extra complexity required to use them.

Somehow, you'll have to translate that into a real date in order to compare it with CURDATE() ... probably a convoluted mathematical formula.

Give me a few minutes, and I'll see what I can come up with.

-- DaveSlash
dimmergeekAuthor Commented:
Thanks, I'm out of the office now, and don't have remote access to the servers from home.  I'll have to try any solution you propose tomorrow from my desk.
Theo KouwenhovenApplication ConsultantCommented:
Your WASTRX isn't a Normal date,  if I look to your example in answer ID: 37840032
It's not a Julian date but  a JDE date (or as they call is a long-julian).

So the selection WHERE WASTRX < CURDATE() - 5 DAYS will not work because of another format.
You have to change the CURDATE() -5 into JDE format (see code):
          STRIP(F4801.WARORN, LEADING, '0' ) = STRIP( DIGITS( F4211.SDDOCO), LEADING, '0' )
          AND F4801.WALNID = FLOOR( F4211.SDLNID )
     WASRST > '93'
     AND WARORN<> ' '
     AND WASTRX <  to_char(to_date(char(CURDATE() -5 days, ISO), 'yyyy-mm-dd'), 'yyyyddd') - 1900000    

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dimmergeekAuthor Commented:
Thanks!  It works beautifully.
Can you please xplain what the conversion things are doing?  I'm confused on the:

WASTRX<to_char(to_date(char(CURDATE() -5 days,ISO),

What is going on here?  Points will certainly be awarded, but I don't want to close the issue until I understand what is happening.

Thank you!
Theo KouwenhovenApplication ConsultantCommented:
Oke Here it is

WASTRX is in JDE format  CYYDDD  (Century 0/1, Year en dau number), in SQL you have CURDATE() or CURRENT DATE that is in a the (unknown) format of your session, so we can't compare that. in the WHERE part

This are the steps

First we have to get the right date result1 = CURDATE() -5 days

Then we convert that to a known format result2=char(result1, ISO)

The format is yyyy-mm-dd, but character and we need a date to convert to daynumber result3= to_date(result2, 'yyyy-mm-dd')
Here we tell result3 that the character-input is a date in format yyyy-mm-dd

Now we have a date field that we can convert to a day number result4=to_char(result3, 'yyyyddd')
Result is a Long-julianday but we need the JDE version of a Short-julianday

For a normal short-julian date you can substring and use the last 5 characters, but JDE like to see 0 for dates in the previous century end a 1 for 2000 and later,
so we have to subtract 1900 from the year, and because our long-jul date has also day numbers 3 pos we subtract 1900000.

You can check is step by step by running a SQL on a small file (you only need one record to get output), try the following to see the results:

Select CURDATE() - 5 days from myfile
Select char(CURDATE() -5 days,ISO) from myfile
Select to_date(char(CURDATE() -5 days,ISO), 'yyyy-mm-dd') from myfile
Select to_char(to_date(char(CURDATE() -5 days,ISO), 'yyyy-mm-dd'),'yyyyddd') from myfile
Select to_char(to_date(char(CURDATE() -5 days,ISO), 'yyyy-mm-dd'),'yyyyddd')-1900000 from myfile

By running this (and play with it) you can see how it is working, it's also possible to go the other way, from JDE to USA format (the one that you called Normal date)

Have fun,
Theo KouwenhovenApplication ConsultantCommented:
Oh and..... dimmergeek,

I really like it when people ask why and how and not just copy...   GREAT!! :-)
The deal is that you don't have "dates" in WASTRX. The field is simply a numeric value that you want SQL to process as if it was a DATE. ("DATE" is upper-cased to emphasize that it's an actual database data type.) SQL knows what to do with DATE data types, but numeric data types don't have the same capabilities.

Murph's functions take a DATE value, i.e., the value returned from the CURDATE() function, and manipulate it into a form that can be compared to WASTRX.

to_char(to_date(char(CURDATE() -5 days, ISO), 'yyyy-mm-dd'), 'yyyyddd') - 1900000

Subtract 5 days from the current date and put it into a date format known as ISO. Store that result as a character value. Let's call that value "x".

to_char(to_date( x , 'yyyy-mm-dd'), 'yyyyddd') - 1900000

Take our character value, x, and turn it into a DATE with a known format. The to_date() function doesn't quite actually exist; it's an alternative syntax element for the TIMESTAMP_FORMAT() function that was recently added to SQL/400. If you had an older system, there are other functions that can be combined to give the same result. But the function really returns a TIMESTAMP value, so the next step is needed. We'll call this result "y".

to_char( y , 'yyyyddd') - 1900000

Take our TIMESTAMP, y, and turn it into a string of characters consisting of the year and day values extracted from the TIMESTAMP. Since we use three characters for the "ddd" part, the function understands the request to mean it should return the day of the year as a julian date requires. The full 4-digit year is part of the result. And since our format string doesn't include anything for the TIME portion, drop hours, minutes and seconds from the TIMESTAMP. The to_char() is like to_date() in that it's actually an alternative for the VARCHAR_FORMAT() function that's been available a little longer than the TIMESTAMP_FORMAT() function. We'll call this string of characters "z".

WASTRX <  z - 1900000

Now that we're down to the very basic string of characters for a 4-digit julian date, use subtraction to turn the first two characters (e.g., '20') into a "century digit". We're pretty sure that our string , z, has nothing but numeric digits; so the subtraction is more or less safe. Check the result to see if it's greater than WASTRX.

As noted by DaveSlash, any potential (not certain at all) space savings is very likely offset by the extra effort always needed to handle this for DATE operations. Further, there are extra precautions needed to ensure that values inserted into fields like this are valid dates.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.