Re: Date function within QUERY definition...

["oludare" <oludare@xxxxxxxxxxxxx>]

Guys,

I thank you all for your input.  I resolved the date problem with the
following:

Sample Date for RIDTE & RDDTE:  20000414

SOLUTION:
==============================
INVDATA     digits(ridte)

INVDTE      date(substr(invdata,5,2)||'/'||            This came out as
04/14/00 (MM/DD/YY)
                        substr(invdata,7,2)||'/'||
                           substr(invdata,3,2))

DUEDATA     digits(rddte)

DUEDTE      date(substr(duedata,5,2)||'/'||         This came out as
04/14/00  (MM/DD/YY)
                           substr(duedata,7,2)||'/'||
                              substr(duedata,3,2))

TODAY       current(date)

PAST         days(today)-days(duedte)                # of Days Overdue, USED
FOR CALCULATION

INVDYS      days(today)-days(invdte)                Invoice # of Days

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




----- Original Message -----
From: "Alex A. Moore" <alexm@ellishosiery.com>
To: <MIDRANGE-L@midrange.com>
Sent: Friday, April 14, 2000 7:00 AM
Subject: 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
> +---

+---
| 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
+---