INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

AS400 Query, Define Result Fields, Date/Time

AS400 Query, Define Result Fields, Date/Time

(OP)
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!!

RE: AS400 Query, Define Result Fields, Date/Time

CODE

                              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

CODE

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.

 

RE: AS400 Query, Define Result Fields, Date/Time

(OP)
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?

RE: AS400 Query, Define Result Fields, Date/Time

Change your definitions to:

CODE

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.

 

RE: AS400 Query, Define Result Fields, Date/Time

(OP)
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

RE: AS400 Query, Define Result Fields, Date/Time

Is ISADATE a date field or a timestamp field?
 

Da mihi sis crustum Etruscum cum omnibus in eo.

 

RE: AS400 Query, Define Result Fields, Date/Time

(OP)
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.

RE: AS400 Query, Define Result Fields, Date/Time

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.

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close