RE: Query problems

[Terry Richardson <RichardsonT@xxxxxxxxx>]

Jim-I don't know that it's any easier, but here's a method for changing a
numeric date field into a real date that Query can process as such.  

Assuming the field NUMDAT is 8,0, YYYYMMDD  and your job/system date format
is *MDY and date separator is '/',
Define result fields    CHRDAT    DIGITS(NUMDAT)  
                                        CMPDAT  SUBSTR(CHRDAT,5,2) || '/' ||
        
SUBSTR(CHRDAT,3,2) || '/' ||
        
SUBSTR(CHRDAT,7,2) || '/' ||
                                
After this, the CMPDAT field looks like MM/DD/YY.


Terry Richardson
Sr. P/A
The Orvis Company, Inc.
802-362-8663


        -----Original Message-----
        From:   Jim Langston [SMTP:jlangston@conexfreight.com]
        Sent:   Thursday, September 16, 1999 11:11 AM
        To:     'MIDRANGE-L@midrange.com'
        Subject:        Query problems

        I am having a problem in a query, that I did a work around for, but
        don't' know why it happens.

        We have some dates in MMDDYY format stored as numbers.

        In a query I define some result fields as:
        Temp1      ARINDT / 100
        Temp2      Temp1 * 100
        InvYear     ARINDT - Temp2
        InvMonth   ARINDT / 10000

        Then, in my select records I have
                    InvYear      EQ     98
        AND    InvMonth   GE     10
        OR       InvYear      EQ     99
        AND    InvMonth    LE     03

        The whole idea being to get the range of records from 10/01/98 to
        03/31/99

        Well, it works but... I do no have any data for 03/xx/99  I have
January
        and February but not March, and I don't know why.

        To resolve this I finally made yet some more result fields:

        Temp3         InvYear * 100
        InvYYMM   Temp3 + InvMonth

        Then, I select for
                   InvYYMM   GE    9810
        AND  InvYYMM    LE    9903

        Which works, and gives me all the records.

        Is this a bug I'm looking at?  I'm on V3R7M0

        And do you know of an easier way to do this?

        Regards,

        Jim Langston

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