RE: Query problems

[Joel Fritz <JFritz@xxxxxxxxxxxxxxxx>]

I don't know what's going on, but here's a couple of alternate techniques
that both let you do direct range comparisons:

Use the digits() function and build a YYMMDD character date result field by
substringing and concatenation.  This one runs into Y2K problems.

It's unpleasant, but you might look at converting the numeric dates to date
data types in your query.  You need to convert the numeric date to a
character field with separators in the job date format similar to the
previous technique and apply the date() function to it. Once you've done the
conversion, you can use it as part of your selection criteria.  This one
will use the system date window.  

Either way the conversion involves an annoying amount of typing compared
with what you can do with the dates in RPG, though. 

> -----Original Message-----
> From: Jim Langston [mailto:jlangston@conexfreight.com]
> Sent: Thursday, September 16, 1999 8: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
+---