Page 1 of 1

creating Date card at run time

PostPosted: Tue Jan 26, 2016 2:21 am
by Farhaan4mf
Hello Everyone -

I have a requirement where my Job is scheduled to run on every 15th and Last day of each month. (i.e. 15th jan, 31st JAN , 15th feb, 29 th Feb, 15 th APR, 30th APR etc. etc.)
My Job creates an extract from DB2 tables. Conditions are
a) When job runs on 15th the extract should be created from First day of the month till 15th of the month
b) When it Runs on Last day of the month, it should consider the date Between 16th of the month till 30th (if April, June, sept etc) , or from 16th to 31st (if JAN, MAR, MAY, July etc)
I have a sample code but it is not applicable for all the scenario. Can anyone help please
SELECT DISTINCT                             
TP.GROUP_ID "GROUP",                         
TP.SPONSOR_NAME "GROUP NAME",               
TP.DIV_ID "DIVISION",                       
FROM DBCYXXX.TPAA TP                         
WHERE                                       
TP.ROLE IN ('P','A')                         
AND DATE(TD.CHN_TS)                         
BETWEEN                                     
(                                           
DATE('0001-01-01') +                         
YEAR(CURRENT DATE) YEARS - 1 YEARS +         
MONTH(CURRENT DATE) MONTHS - 1 MONTHS+       
DAY(CURRENT DATE) DAYS - 1 DAYS - 14 DAYS   
)                                           
AND                                         
(                                           
DATE('0001-01-01') +                         
YEAR(CURRENT DATE) YEARS - 1 YEARS +         
MONTH(CURRENT DATE) MONTHS - 1 MONTHS +     
DAY(CURRENT DATE) DAYS - 1 DAYS             
)                                           
ORDER BY TP.GROUP_ID DESC                   
;                                           

Re: creating Date card at run time

PostPosted: Tue Jan 26, 2016 4:11 pm
by NicC
Instead of trying to do it in SQL why not have the dates calculated by the program that issues the SELECT?

Re: creating Date card at run time

PostPosted: Tue Jan 26, 2016 9:24 pm
by Farhaan4mf
Hello NicC -
Actually I do not have a program. I am running that SQL in my JCL itself. Is it feasible to do in JCL itself ?