how to default current date when selecting records in wrkqry

dmhopkins
dmhopkins used Ask the Experts™
on
This is such a beginners question that I'm almost ashamed to ask but I'm gonna anyway.  I am building a query using wrkqry in an AS/400 environment.  I want to check a file date against the current system date.  Does wrkqry recognize the system date?  If so what do I call it in my "Values" field.

Thanks All.    
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Create a result field with the current date:

TODAY       char(current(timestamp))    
THISYEAR    substr(today,1,4)      

The char function changes the date to a string so you can manipulate it.
CURRENT can also return just DATE and TIME.

Hope this helps!
Barry
Gary PattersonVP Technology / Senior Consultant

Commented:
Also, what format is the date in yor file?  Is it a DATE field, a numeric field, or a character field?  If numeric or character, how is the date formatted (give an example, please).

The current() function returns a value in DATE, TIME, TIMESTAMP format, depending on the parameter you select.

If your date is stored in DATE format, you can you can just compare to current(date).  If it is in numeric or character format, you have to do a bit more work to get both the system date that you get from current().  In Barry's example above, he uses the CHAR function to convert the TIMESTAMP returned by current() to CHAR format.  Dates stored in numeric format are even trickier, and generally require that you convert the numeric date into DATE format using a combination of DIGITS, concatenation, and DATE functions.

If you'll post back with the date format that you need to compare, we can give you a specific example.

- Gary Patterson

Author

Commented:
Guys thank you so very much!  I got it now just needed you to remind me where to begin.  By the way the dates are defined in DATE format so I was able to use both your examples to get what I needed.  You two ROCK!!!!!!!
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Gary PattersonVP Technology / Senior Consultant

Commented:
In that case, current(DATE) was the answer you needed, so points to Barry, please.

- Gary

Author

Commented:
Duely noted.  Thank you both for taking the time to answer my putzy question.  And Barry thank you for giving me the answer I needed.
You're welcome. No such thing as a putzy question.
Take care
Barry
Dirk HaestProject manager

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial