Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to get 1st and last day of the month

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
alluri12

New User


Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA

PostPosted: Fri May 11, 2007 12:32 am    Post subject: How to get 1st and last day of the month
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 11, 2007 2:25 am    Post subject:
Reply with quote

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
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Fri May 11, 2007 2:52 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 11, 2007 4:46 am    Post subject:
Reply with quote

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
View user's profile Send private message
alluri12

New User


Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA

PostPosted: Fri May 11, 2007 8:30 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Ravi

New User


Joined: 23 May 2003
Posts: 3
Location: Chennai

PostPosted: Thu Jul 30, 2009 3:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
prashant_2420

New User


Joined: 06 Jan 2010
Posts: 2
Location: Oregon

PostPosted: Thu Feb 04, 2010 12:12 am    Post subject:
Reply with quote

This information proved to be very useful for me. Thanks everybody!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Feb 04, 2010 12:29 am    Post subject: Reply to: How to get 1st and last day of the month
Reply with quote

Hello and welcome to the forum,

Good to hear we have some things that are useful icon_smile.gif

Enjoy the forum,

d
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 205
Location: Bangalore

PostPosted: Thu Feb 04, 2010 12:37 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Thu Feb 04, 2010 4:07 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Reg:Displaying the particular month o... bhavana yalavarthi All Other Mainframe Topics 4 Sat Jul 08, 2017 8:13 pm
No new posts SORT to append Month and YEAR in MMYY... tisamf DFSORT/ICETOOL 1 Wed Mar 08, 2017 4:46 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts How to get previous month in SYMNAMES Suganya87 SYNCSORT 9 Fri Oct 28, 2016 1:37 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us