Code: |
Select col1, col2 From table1 WHERE PR_YR = :Wv-YR AND PR_MO = :Wv-Mo AND SALE_DT BETWEEN LAST_DAY(CURRENT DATE) + 1 DAY- 3 MONTH AND LAST_DAY(CURRENT DATE - 2 MONTH) |
Code: |
SELECT LAST_DAY(CURRENT DATE - 3 MONTH) + 1 DAY ,LAST_DAY(CURRENT DATE - 2 MONTH) FROM SYSIBM.SYSDUMMY1 ; |
Code: |
SELECT LAST_DAY( DATE('2017-09-30') - 3 MONTH) + 1 DAY ,LAST_DAY(DATE('2017-09-30') - 2 MONTH) FROM SYSIBM.SYSDUMMY1 COL1 COL2 ---------- ---------- 2017-07-01 2017-07-31 |
kolusu wrote: |
Suchay,
You have an uncanny way of making the requirements complicated. You need to design generic solutions. |
Quote: |
Basically, your query can run on any day, but it needs to get the current_month -2 months start and begin dates? but if runs on the month end then you need to get the current_month -1 months start and begin dates? Is that it? What happens when you run this job in the middle of the month? or for some reason, the job failed on September 30th but then it couldn't be restarted till Oct 1st? |
Suchay wrote: |
We have two columns and I have mentioned in my post Wv-YR = 2017, Wv-Mo = 09, which is process month and year
|
Code: |
SELECT DATE('2017'||'-'||'09'||'-'||'01') - 1 MONTH ,LAST_DAY(DATE('2017'||'-'||'09'||'-'||'01') - 1 MONTH) FROM SYSIBM.SYSDUMMY1 ; |
Code: |
2017-08-01 2017-08-31 |
Code: |
SELECT DATE(:WV-YR ||'-'||:WV-MO||'-'||'01') - 1 MONTH ,LAST_DAY(DATE(:WV-YR ||'-'||:WV-MO||'-'||'01') - 1 MONTH) FROM SYSIBM.SYSDUMMY1 ; |
output generated using printer-friendly topic mod. All times are GMT - 5 Hours