|
View previous topic :: View next topic
|
| Author |
Message |
alluri12
New User
Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA
|
|
|
|
Hi,
I have a query that needs to be changed every month. Could anybody please suggest me how to get this automatically
SELECT T2.ID
FROM TC.TABLE T1,
TC.TABLE T2
WHERE T1.COL1 = T2.COL2
AND T1.BEG_DATE <= '2007-03-31' ---> LAST DAY OF THE PREVIOUS MONTH
AND T1.EXPR_DATE >= '2007-03-01' ---> FIRST DAY OF THE PREVIOUS MONTH.
WITH UR |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
One way would be to have the month presented as a parm or other input. The code would no longer have a literal coded, but would have references to first and last day variables built based on the parm.
If you talk with your scheduling people, they can probably suggest a way to automate this for the monthly run, then nothing need be done manually unless there is some kind of exception run. |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
With COBOL intrinsic functions, you can subtract one from the current month (adjusting for Jan-Dec) and plug in "01" for the first day of last month; And take the current month and plug in an "01" for the first day, convert to integer days, subtract one and convert back and you have the last day of last month.
Saying that, I'm sure DB2 has similar date manipulation functions, haven't you yet found them in the Fine Manual? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
I was thinking that the scheduling people would know which month the run was for - including any "special" runs and could handle this via the scheduler.
If the job will always be consistent as far as running, you can use:
SELECT CURRENT DATE AS DATE FROM SYSIBM.SYSDUMMY1 which will return yyyy-mm-dd. Then you can set the first and last day as needed.
Here's a link to a bunch of other date/time manipulations - called DB2 Basics: Fun with Dates and Times
http://www-128.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html |
|
| Back to top |
|
|
alluri12
New User
Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA
|
|
|
|
Thanks Everbody... I am able to resolve this ...... Here is the SQL...
SELECT LAST_DAY(CURRENT_DATE) - 1 MONTH AS DATE1,
LAST_DAY(CURRENT_DATE) - 2 MONTHS + 1 DAY AS DATE2
FROM SYSIBM.LUNAMES |
|
| Back to top |
|
|
Ravi
New User
Joined: 23 May 2003
Posts: 3
Location: Chennai
|
|
|
|
The below is incorrect. This code will fail if current month has last day as 30th and previous month as 31.
| Code: |
SELECT LAST_DAY(CURRENT_DATE) - 1 MONTH AS DATE1,
LAST_DAY(CURRENT_DATE) - 2 MONTHS + 1 DAY AS DATE2
FROM SYSIBM.LUNAMES |
The correct code is
| Code: |
SELECT LAST_DAY(CURRENT_DATE - 1 MONTH) AS DATE1,
LAST_DAY(CURRENT_DATE - 2 MONTHS) + 1 DAY AS DATE2
FROM SYSIBM.LUNAMES |
Hope this helps |
|
| Back to top |
|
|
prashant_2420
New User
Joined: 06 Jan 2010
Posts: 2
Location: Oregon
|
|
|
|
| This information proved to be very useful for me. Thanks everybody! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
Good to hear we have some things that are useful 
Enjoy the forum,
d |
|
| Back to top |
|
|
Ajay Baghel
Active User
.jpg)
Joined: 25 Apr 2007
Posts: 206
Location: Bangalore
|
|
|
|
Nice learning.
SELECT LAST_DAY(CURRENT_DATE - 2 MONTHS) AS LASTDAYOFDEC2009,
LAST_DAY(CURRENT_DATE - 3 MONTHS) + 2 DAYS AS SECONDAYOFDEC2009
FROM SYSIBM.SYSDUMMY1;
O/P
LASTDAYOFDEC2009 SECONDAYOFDEC2009
---------------- -----------------
12/31/2009 12/02/2009 |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
| Ajay Baghel wrote: |
Nice learning.
SELECT LAST_DAY(CURRENT_DATE - 2 MONTHS) AS LASTDAYOFDEC2009,
LAST_DAY(CURRENT_DATE - 3 MONTHS) + 2 DAYS AS SECONDAYOFDEC2009
FROM SYSIBM.SYSDUMMY1;
O/P
LASTDAYOFDEC2009 SECONDAYOFDEC2009
---------------- -----------------
12/31/2009 12/02/2009 |
Instead of using these dummy selects, you can write:
| Code: |
SET (:LASTDAYOFDEC2009, :SECONDDAYOFDEC2009) = LAST_DAY(CURRENT_DATE - 2 MONTHS),
LAST_DAY(CURRENT_DATE - 3 MONTHS) + 2 DAYS |
to get your calculated dates assigned to host variables. |
|
| Back to top |
|
|
|
|
