OnDemand User Group

Support Forums => Other => Topic started by: donna.gregory on March 25, 2022, 10:40:53 AM

Title: Query Restrictions - How to filter based on current date
Post by: donna.gregory on March 25, 2022, 10:40:53 AM

Need help coding a query restriction so that only documents appear where the current date is <= to an archive date field.    I am unable to figure out how to include the current date into the query statement.   I am on version is 10.5.

Title: Re: Query Restrictions - How to filter based on current date
Post by: Darrell Bryant on March 25, 2022, 11:53:43 AM

You don't mention which database you are using. Db2 supports 'CURRENT DATE'.  Quoting from the z/OS Db2 documentation:
"The CURRENT DATE special register specifies a date that is based on a reading of the time-of-day clock when the SQL statement is executed at the current server."
and this
"Specifying CURRENT_DATE is equivalent to specifying CURRENT DATE"

If this is my data, where JLDATE has a data type of Date:
JOBNAME     JLDATE   
DSP01       05/24/15
ONDUNI1     01/10/17
QP0ZSPWT    02/03/17
NC4057D0    03/25/22
QP0ZSPWT    06/30/22

When I run this SQL:
SELECT JOBNAME, JLDATE FROM ARCHIVE.WGSM1 WHERE JLDATE <= CURRENT_DATE ORDER BY JLDATE

I receive this result:
JOBNAME     JLDATE 
DSP01       05/24/15
ONDUNI1     01/10/17
QP0ZSPWT    02/03/17
NC4057D0    03/25/22

Or to flip that around, this SQL statement:
SELECT JOBNAME, JLDATE FROM ONDENU.WGSM1 WHERE CURRENT_DATE <= JLDATE

Produces this result:
JOBNAME     JLDATE 
NC4057D0    03/25/22
QP0ZSPWT    06/30/22

I tested this with a query restriction, you have to enter the query restriction using the keyboard, and not using the "BuildSQL" function.  I used this query restriction:

JLDATE <= CURRENT_DATE

And in the client the same two rows listed above are returned.

Title: Re: Query Restrictions - How to filter based on current date
Post by: donna.gregory on March 25, 2022, 12:14:47 PM

Thank you!!   It is working now.   The key information you provided for me was to not use the "BuildSQL" function.   I was using this and was throwing an error when I tried to save it.   Thank you again!!!