AS400 Query, Define Result Fields, Date/Time 2

[sueshe]

I am using the AS400 Query, we are in version V05R03M00 IBM iseries. I am building a query that I need to get all record 72 hrs prior. I have been trying to set up define results fields for this with no luck. I set up so that HRS72 TODAY - 3 days, but then couldn't select records so that ISADATE GE HRS72. So, I tried again using
TODAY CURRENT(TIMESTAMP)
HRS72 TODAY - 72 HOURS
and it keeps telling me HRS72 needs to be a date. The report will be set up to run at midnight each night and will need all records that ISADATE YYYY-MM-DD shows as being 72 hrs prior til now(or 3 days). There is a time field XX:XX too, which I tried using with timestamp, but don't think I understand enough about it. I also tried doing HRS72 TODAY - 3 DAYS and no records came up when I tried to select records ISADATE GE HRS72, but there are a lot of records. Hoping someone can help me with this, have worked for hours and still come up with nothing!!

 

[flapeyre]

                              Define Result Fields                              
                                                                                
 Type definitions using field names or constants and operators, press Enter.    
   Operators:  +, -, *, /, SUBSTR, ||, DATE...                                  
                                                                                
 Field       Expression                         Column Heading        Len   Dec 
 TODAY       current(date)                                                      
                                                                                
                                                                                
                                                                                
 HRS72       today - 3 days

will give you

TODAY     HRS72    
05/30/08  05/27/08

I am assuming that ISADATE is a "real" date, and not defined as numeric or character.

Da mihi sis crustum Etruscum cum omnibus in eo.

 

[sueshe]

yes, I got that far for both TODAY and HRS72. My problem is that when I go into select records, no records come up.

ISADATE GE HRS72

ISADATE shows on record as 2008-05-01
HRS72 shows on record as 05/01/08

I even tried doing ADMDT DATE(ISADATE)
to have it show up the same, then tried doing select records as

ADMDT GE HRS72

but still no records?

 

[flapeyre]

Change your definitions to:

Field       Expression                       
TODAY       char(current(date),ISO)          
                                             
                                             
                                             
HRS72       char(current(date) - 3 days,ISO)

Add a result field defined as char(ISADATE,ISO)

Use that to compare with HRS72.

Da mihi sis crustum Etruscum cum omnibus in eo.

 

[sueshe]

Shoot, and I was thinking that would work. I'm still getting no records

TODAY CHAR(CURRENT(DATE),ISO)
HRS72 CHAR(CURRENT(DATE) - 3 DAYS,ISO)
ADMDT CHAR(ISADATE,ISO)

select records

ADMDT GE HRS72

 

[flapeyre]

Is ISADATE a date field or a timestamp field?


Da mihi sis crustum Etruscum cum omnibus in eo.

 

[sueshe]

I'm actually not certain what kind of field it is, it says ZONED in the file definition. I can do select records by typing in

ISADATE RANGE '2008-05-27' '2008-05-30'

and it comes out with the correct records.

 

[flapeyre]

Zoned means it's a numeric field, not a "real" date. In that case, it would not let you use quotes or separators.

Da mihi sis crustum Etruscum cum omnibus in eo.