Hi all

I'm trying to bring back data with certain criteria but am struggling with the date criteria. I need to fetch data from an "APPLICATION_DATE" column but the date needs to be 12 or more months ago from current date. I am using version 8.1 on z/OS. Please let me know if you need any other details.

Thanks
Andre van Zyl

>= current date - 1 years

Try this:
"select current_date - 12 months from sysibm.sysdummy1"
and read this Universe - A Global Village: DB2 Basics: Fun With Date And Time

Thanks for the quick replies...

Here is the code I'm using but am receiving an error(see attachment):

SELECT DISTINCT A.MASTER_ID,
A.COMPANY_CODE,
C.DIRECTORY_ID,
B.PLAN_CODE,
B.STRT_OF_PRM_ACT_DT,
B.APPLICATION_DATE,
B.RB_CVG_STATUS,
B.PARENT_ID,
B.CVG_TYPE,
A.POLICY_STATUS,
D.INSURED_TYPE
FROM GS20.CONTT A,
GS20.CVGT B,
GS20.ROLES C,
GS20.DPXXT D
WHERE A.MASTER_ID = B.MASTER_ID AND
A.POLICY_STATUS IN ('A','P','G') AND
B.RB_CVG_STATUS IN ('A') AND
A.COMPANY_CODE = B.COMPANY_CODE AND
A.MASTER_ID = C.MASTER_ID AND
A.COMPANY_CODE = C.COMPANY_CODE AND
A.RESTRICTION_CODE = '' AND
A.MASTER_ID IN (SELECT MASTER_ID FROM GS20.CVGT
WHERE B.MASTER_ID NOT LIKE '69355UCB%' GROUP BY MASTER_ID HAVING COUNT(*) = 1) AND
B.APPLICATION_DATE >= CURRENT DATE - 1 YEARS
FETCH FIRST 5 ROWS ONLY

Thank You
Andre van Zyl

What is the data type of APPLICATION_DATE column of GS20.CVGT table?

Hi Tonkuma

The data type is DECIMAL(8, 0)

Thanks

Apart from the error SQL0401N, following part of your query looks strange for me.
I thought that result of your query would be same, if B.MASTER_ID NOT LIKE '69355UCB%' was moved to WHERE clause of main query.
Because, the subquery will return null(no row), if B.MASTER_ID LIKE '69355UCB%'. Then, WHERE condition of main query would result to false.

If B.MASTER_ID NOT LIKE '69355UCB%', the subquery will be equivalent to:
Where B.MASTER_ID would be a column of the following table of your query:

What is the format of the column?
Is it yyyymmdd?

If the column was DECIMAL(8, 0) with format "yyyymmdd.",
then my idea is "convert it to date, then use date calculation of DB2(like Rahul Singh and dr_te_z wrote)".

Here is an example:
DATE( TRANSLATE('abcd-ef-gh', CHAR(B.APPLICATION_DATE), ' abcdefgh.') ) >= CURRENT DATE - 1 YEAR

This was not tested.
But, I saw PDF manual "DB2 Universal Database for z/OS SQL Reference Version 8".