IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Obtain last day of next month


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
aneeta

New User


Joined: 08 Dec 2005
Posts: 48
Location: Chennai

PostPosted: Fri Aug 15, 2008 4:31 pm
Reply with quote

Hi,
My jobs runs on every month end..Eg Aug 31.I need to get the next month end using SQL query.Eg Sep 30 when it runs on Aug 31.
Basically get the next month end when the job runs for current month end.

Can someone pls help me with the query.This is a urgent requierement.

Thanks
Aneeta
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Aug 15, 2008 4:55 pm
Reply with quote

Not a db2 solution, but is accurate and faster than using db2:
(stolen from Kolusu)

Code:

01 WS-MONTH-END-DD             PIC X(24) VALUE             
                               '312831303130313130313031'.
01 WS-TBL-MONTH-END REDEFINES WS-MONTH-END-DD.             
    05 TBL-MONTH-END-DAY       PIC 9(02) OCCURS 12 TIMES. 

01 WS-GREG-DATE.
   05 WS-GREG-YEAR             PIC 9(04).
   05 WS-GREG-MNTH             PIC 9(02).
   05 WS-GREG-DAY              PIC 9(02).

EVALUATE TRUE                                     
    WHEN FUNCTION MOD (WS-GREG-YEAR 4)   NOT ZERO   
    WHEN FUNCTION MOD (WS-GREG-YEAR 100)     ZERO     
     AND FUNCTION MOD (WS-GREG-YEAR 400) NOT ZERO
    MOVE '28' TO WS-TBL-MONTH-END (3: 2)         
WHEN OTHER                                       
    MOVE '29' TO WS-TBL-MONTH-END (3: 2)         
END-EVALUATE                                     
                                                 
MOVE TBL-MONTH-END-DAY(WS-GREG-MNTH)             
                           TO WS-GREG-DAY
       
DISPLAY 'LAST-DATE OF MONTH:' WS-GREG-DATE       
Back to top
View user's profile Send private message
ulfnsc

New User


Joined: 15 Jan 2004
Posts: 19
Location: Stockholm, SWEDEN

PostPosted: Fri Aug 15, 2008 5:47 pm
Reply with quote

What if the job run's in dec 2008 (20081231) ??
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Aug 15, 2008 5:55 pm
Reply with quote

He only asked for month end.

besides, it is not a db2 solution.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Aug 15, 2008 6:42 pm
Reply with quote

Since Ulf pointed out that my routine sucked, how about using the last_day function:

Code:

SELECT LAST_DAY('2008-01-01')
FROM SYSIBM.SYSDUMMY1
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Aug 15, 2008 7:24 pm
Reply with quote

or even better. if your date is aug 31

Code:

SELECT LAST_DAY(DATE('2008-08-31') + 1 MONTH )
FROM SYSIBM.SYSDUMMY1


of course you can do this with host variables
Back to top
View user's profile Send private message
aneeta

New User


Joined: 08 Dec 2005
Posts: 48
Location: Chennai

PostPosted: Fri Aug 15, 2008 10:56 pm
Reply with quote

Hi,
I tried the last query and its throwing sysntax error.

Thanks
Aneeta
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Sat Aug 16, 2008 12:16 am
Reply with quote

Hi

For me, Dick query is working fine and returning result. Please let us know the error you are getting?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sat Aug 16, 2008 12:31 am
Reply with quote

Hello,

Quote:
I tried the last query and its throwing sysntax error.
Possibly a copy/paste or typing error.

As requested, please post the error as well as the query that gives the error (use copy/paste for both the query and the error).
Back to top
View user's profile Send private message
aneeta

New User


Joined: 08 Dec 2005
Posts: 48
Location: Chennai

PostPosted: Wed Aug 20, 2008 9:27 am
Reply with quote

Hi All.
This is the error message I get.
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -20101, ERROR: THE FUNCTION LAST_DAY FAILED WITH REASON
00E73004
DSNT418I SQLSTATE = 56060 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRLET SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 1050 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0000041A' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,,,,, SQL WARNINGS
DSNT417I SQLWARN6-A = W,,,, SQL WARNINGS

I checked that the field LAST_DAY is not found in the SYSDUMMY1 Table.

Pls help me.

Thanks
Aneeta
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Aug 20, 2008 11:16 am
Reply with quote

what columns did you find in SYSIBM.SYSDUMMY1 ?
Back to top
View user's profile Send private message
aneeta

New User


Joined: 08 Dec 2005
Posts: 48
Location: Chennai

PostPosted: Thu Aug 21, 2008 1:08 pm
Reply with quote

hI,
When I execute this query I get the output but not for last day.

SELECT CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1;

Pls help me resolve this.I need to complete this by THURDSAY.

Thanks
Aneeta
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 21, 2008 1:16 pm
Reply with quote

as Dick asked, please post your query......... this is thursday and it is your deadline, not ours.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Aug 21, 2008 2:30 pm
Reply with quote

You can try the following query.

If you run this query on 31st August, the output will be 2008-09-30
SELECT (CURRENT DATE + 1 MONTHS) FROM SYSIBM.SYSDUMMY1;
Back to top
View user's profile Send private message
aneeta

New User


Joined: 08 Dec 2005
Posts: 48
Location: Chennai

PostPosted: Thu Aug 21, 2008 9:55 pm
Reply with quote

SELECT LAST_DAY(DATE('2008-08-31') + 1 MONTH )
FROM SYSIBM.SYSDUMMY1;


DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -20101, ERROR: THE FUNCTION LAST_DAY FAILED WITH REASON
00E73004
DSNT418I SQLSTATE = 56060 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRLET SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 1050 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0000041A' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,,,,, SQL WARNINGS
DSNT417I SQLWARN6-A = W,,,, SQL WARNINGS
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 2
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 24
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Aug 22, 2008 12:42 am
Reply with quote

This is the Db2 Vsn 7 reason:
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNMCH17/4.21.47?SHELF=&DT=20061207111438&CASE=

This is the DB2 Vsn 8 reason:
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNC1J12/3.20.50?SHELF=&DT=20070111144235&CASE=

This is the Db2 Vsn 9 reason:
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNC1K11/3.21?DT=20070917024311


you can (you should) download the pdf for whichever version your systems running. (the pdf symbol at the upper right of any IBM document is the -download this pdf- button)

Suggest you download so that you can talk to your dba.

If you are running at a site with version 6 or less,
suggest you find a job with another company.
Back to top
View user's profile Send private message
aneeta

New User


Joined: 08 Dec 2005
Posts: 48
Location: Chennai

PostPosted: Fri Aug 22, 2008 1:14 am
Reply with quote

Hi,
When I try thus query-
SELECT (DATE('2008-08-31') + 1 MONTHS )
FROM SYSIBM.SYSDUMMY1;

I get the result as 2008-09-30

But when I try this query for the next month end,

SELECT (DATE('2008-09-30') + 1 MONTHS )
FROM SYSIBM.SYSDUMMY1;

I get the result as 2008-10-30 instead of 2008-10-31.

Can I get help on this..I am try to complete this with version of DB2 i currently have as I am not able to use LAST_DAY function.

Thanks
Aneeta
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Aug 22, 2008 5:47 am
Reply with quote

This will always return the first day of the current month
Code:
SELECT CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS
FROM SYSIBM.SYSDUMMY1


This will always return the last day of the current month without using the last_day function
Code:
SELECT CURRENT DATE - (DAY(CURRENT DATE) - 1 ) DAYS + 1 MONTHS - 1 DAYS 
FROM SYSIBM.SYSDUMMY1;                                                 


For the last day of the next month without using the last_day function
Code:
SELECT CURRENT DATE - (DAY(CURRENT DATE) - 1 ) DAYS + 2 MONTHS - 1 DAYS 
FROM SYSIBM.SYSDUMMY1;                                                 
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sun Aug 24, 2008 8:47 am
Reply with quote

Hi Craig,

Cool icon_smile.gif

I deleted my earlier post about experimenting with some other dates.

Then i thought - will these also work for the earlier releases of db2?

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Populate last day of the Month in MMD... SYNCSORT 2
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts replace word 'MONTH' with current mon... SYNCSORT 11
No new posts Help, trying to use OVERLAY to get a ... DFSORT/ICETOOL 3
No new posts To get previous month from current ti... SYNCSORT 7
Search our Forums:

Back to Top