×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

AS400 Query, Subtract 24 hours

AS400 Query, Subtract 24 hours

AS400 Query, Subtract 24 hours

(OP)
Hi....am trying to build a query in the AS400, we are currently using, V05R03M00 IBM AS400.  In my query, I have two fields:  DATECOLL 'yyyy-mm-dd' and TIMECOLL 'hh:mm:ss'.
I need all tests that were collected 24 hours ago from the date/time the query is run.  I've tried everything I can think of and keep getting incorrect information.  Can someone help me with this?  I need it to be set up in define results field so that I can schedule it to run on it's own every morning at 1am.  Would appreciate any help I can get!!!  Thank you!!!

RE: AS400 Query, Subtract 24 hours

Define result fields...

YESTDATE = CHAR(CURRENT(DATE) - 1 DAY, ISO)
YESTTIME = CURRENT(TIME) - 24 HOURS

If you want to run it for yesterday's results, just say
DATECOLL EQ YESTDATE

If you need a RANGE, that will have to work differently. Let me know if you need more.

Mark

RE: AS400 Query, Subtract 24 hours

(OP)
Thanks so much!!! That worked perfect....I think I was making it more difficult than it was!!!!  Thanks again!

RE: AS400 Query, Subtract 24 hours

I have a variabl field, and want to select a range of dates from 2 months before cureent date , until current date.

RE: AS400 Query, Subtract 24 hours

Use the same logic.

Define results.
YESTDATE = CHAR(CURRENT(DATE) - 2 MONTHS, ISO)

Select records.
DATECOLL GE YESTDATE

or for a range two months ago through yesterday...

Define results.
LMONTH = CHAR(CURRENT(DATE) - 2 MONTHS, ISO)
YESTDATE = CHAR(CURRENT(DATE) - 1 DAY, ISO)

Select records.
DATECOLL RANGE LMONTH YESTDATE

Mark

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!

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