AS400 Query, Subtract 24 hours 1

[sueshe4]

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!!!

 

[Kozusnik]

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

 

[sueshe4]

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

 

[baruch]

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

 

[Kozusnik]

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