Find # days between 2 dates in Query 400

120 pts.
AS/400 Query
iSeries development
I have never used this function but it looks like the startdate and enddate must be alpha expressions for this to work. Is that correct?

Answer Wiki

Thanks. We'll let you know when a new response is added.

This Answer may help.


Unfortunately, that referenced answer doesn’t address the question.

No, the startdate and enddate fields should be DATE or TIMESTAMP fields or be DATE expressions. You may convert various character fields into “dates” with a variety of functions.

Overall, you shouldn’t be using Query/400 at all. Use Query Manager instead. See the STRQM command. (It might not be installed. SQL options are needed, though experience can show you how to create and use QM queries even without having the SQL options product installed.)


Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • RossHowatson
    If the fields are defined as character then convert the date field to a YYYY-MM-DD format (include the - symbol) via a Define Result Fields as follows: OLDESTCHR1 substr(chr1,1,4) || '-' || substr(chr1,5,2) || '-' || substr(chr1,7,2) (change the start positions accordingly). Do the above for both date fields. DATECHRDIF date(OLDESTCHR2) - date(OLDESTCHR1) This will give you the number of days between two dates (0 days when dates are the same). If the fields are already defined as date (in YYYY-MM-DD format) then the formula is as follows: DATEDIFF DateField2 - DateField1
    630 pointsBadges:
  • WoodEngineer
    A number of years ago in our shop we implemented a date file for such things. This was before some of the handy date functions become readily available. The file contains dates for many years. Each record contains the date in various formats plus the date number following the same numbering as Excel. To determine the number of days between two dates we chain to the file with each of the dates and subtract one date number from the other. Not at all elegant but it works and has proven very handy. Use of this file has dropped off in recent years but we still use it occasionally for situations such as you describe. If you want to know a date 30 days from today, just get today's date number from the file, add 30 to it and retrieve the new date using the new date number as the key. Again, this a really old school but it works nicely in some applications like query.
    8,225 pointsBadges:
  • ToddN2000
    I do not think you can run a macro within a macro. I may be wrong but I'll have to do some digging on my own. Sounds interesting. What type of application are you thinking of the using it in? We do not use a lot of macros here mainly because of security problems. We had a lot users creating macros for their sign on. Some of theses were PC that were shared on the shop floor .When their password was due to be changed their macros no longer worked.
    133,740 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: