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