RE: Date function within QUERY definition...

This works for us:

   BD          digits(prbgdt)
   BDT         date(substr(bd,6,2)||'/'||
               substr(bd,8,2)||'/'||
               substr(bd,4,2))
   ED          digits(preddt)
   EDT         date(substr(ed,6,2)||'/'||
               substr(ed,8,2)||'/'||
               substr(ed,4,2))
   DUR         days(BDT)-days(EDT)

Usually ++++++++++ indicates an invalid value for that type of data, or an
overflow, like a decimal data error or an invalid date in a date field.  Run
your query interactively in debug mode and look for clues in the job log.

Alex Moore ~ Ellis Hosiery Mills, Inc.
alexm@ellishosiery.com

Subject: Re: Date function within QUERY definition...


I have almost identical problem in Query/400 with ccyymmdd (8.0) format BPCS
405 CD numerical fields in user job session using mmddyy (6.0) date format.
I am trying to calculate the number of days difference between two dates in
the Customer Order Change History ... Old date being what WAS the due date &
New date being what the requirements were changed to.  Ultimately I want to
show changes inside lead time days.

I use DIGITS to get ccyymmdd to character field then rearrange with
substring
& *CAT to mmddyy job format, then use DATE to put it into date field & if I
print it at this point, the data is correct info, I don't know if type is
correct but Query is not complaining about what I am doing ... I have two
separate date fields constructed the same way, then I use DAYS to calculate
difference & I always get +++++++++ regardless - even when the two dates are
the same.

I have been on multiple threads of multiple forums & I follow people's
instructions & I get the same garbage out.

> From: oludare@ix.netcom.com (oludare)

>  To:  MIDRANGE-L@midrange.com (AS/400 Midrange Usergroup)
>  CC:  RPG400-L@midrange.com (RPG AS/400 UserGroup)
>
>  Hi guys,
>
>  This is a QUERY question.  I have a numeric field with date value, in
> ccyymmdd format (10,0).  In my query, I redefined this numeric field as an
*
> ISO date using CHAR function then subtract it from system date in *ISO
format,
>  using DAYS function.  The result fields display +++++++++++++++
everywhere
> the numeric field is used, either in calculation or straight display.
>
>  How do I display the value or what I'm I doing wrong?
>
>  Thanks,
>
>  Oludare

Al Macintyre  ©¿©

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---