How to get value from selected date range in as400 query

[BPandya]

Hi,

I am trying to get value in query 400 for the selected date range 11/01/04 to 11/01/05 and I am only getting value for year 04 month 11, 12 and year 05 month 11 only.

I can do this in SQL but just curious to know.


AND/OR Field Test Value (Field, Number, 'Characters', or ...)
CENTURY EQ 1
AND YEAR GE 04
AND MONTH GE 11
AND YEAR LE 05
AND MONTH LE 11

Thanks,

Bharat

[Sbp0718]

Re: How to get value from selected date range in as400 query

It is your MONTH GE 11 that is stopping you.

If you can get the date in a YYMMDD form you can use FldDat between 20041102 and 20051131

[jamief]

Re: How to get value from selected date range in as400 query

Gonna have to use the result field and rebuild the dates to be YYMMDD instead of MMDDYY. You will need to use SUBSTR in result field of the date to pull out the month day and year then use || (CAT) to build it back to YYMMDD

to start from file

MMDDYY    *dec   6    0


result field entries
MMDDYYC     digits(MMDDYY)         
                                 
                                   
                                   
MONTH       SUBSTR(MMDDYYC,1,2)  
DAY         SUBSTR(MMDDYYC,3,2)  
                                 
                                 
                                 
YEAR        SUBSTR(MMDDYYC,5,2)  
YYMMDD      YEAR || MONTH || DAY     


the data 

 Line   ....+....1....+....2....+....3....+....4...  
        MMDDYYC  MONTH  DAY  YEAR  YYMMDD   MMDDYY   
 000001 033006    03    30    06   060330   33,006