Find # days between 2 dates in Query 400
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
May 28, 2008 3:50 PM
UPDATED:
April 23, 2010 2:45 PM
Answer Wiki:
<a href="http://itknowledgeexchange.techtarget.com/itanswers/find-sharp-days-between-2-dates-in-query-400/">This Answer may help</a>
==================================================
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.)
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
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