creating Date card at run time



IBM's flagship relational database management system

creating Date card at run time

Postby Farhaan4mf » Tue Jan 26, 2016 2:21 am

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                   
;                                           
Farhaan4mf
 
Posts: 29
Joined: Mon May 13, 2013 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: creating Date card at run time

Postby NicC » Tue Jan 26, 2016 4:11 pm

Instead of trying to do it in SQL why not have the dates calculated by the program that issues the SELECT?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: creating Date card at run time

Postby Farhaan4mf » Tue Jan 26, 2016 9:24 pm

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 ?
Farhaan4mf
 
Posts: 29
Joined: Mon May 13, 2013 4:53 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post