Obtain last day of next month

aneeta · New User Joined: 08 Dec 2005 Posts: 48 Location: Chennai

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

dbzTHEdinosauer · Posted: Fri Aug 15, 2008 4:55 pm

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

ulfnsc · Posted: Fri Aug 15, 2008 5:47 pm

What if the job run's in dec 2008 (20081231) ??

dbzTHEdinosauer · Posted: Fri Aug 15, 2008 5:55 pm

He only asked for month end.

besides, it is not a db2 solution.

dbzTHEdinosauer · Posted: Fri Aug 15, 2008 6:42 pm

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

dbzTHEdinosauer · Posted: Fri Aug 15, 2008 7:24 pm

or even better. if your date is aug 31

aneeta · New User Joined: 08 Dec 2005 Posts: 48 Location: Chennai

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

Thanks
Aneeta

Suresh Ponnusamy · Posted: Sat Aug 16, 2008 12:16 am

Hi

For me, Dick query is working fine and returning result. Please let us know the error you are getting?

dick scherrer · Posted: Sat Aug 16, 2008 12:31 am

Hello,

aneeta · New User Joined: 08 Dec 2005 Posts: 48 Location: Chennai

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

dbzTHEdinosauer · Posted: Wed Aug 20, 2008 11:16 am

what columns did you find in SYSIBM.SYSDUMMY1 ?

aneeta · New User Joined: 08 Dec 2005 Posts: 48 Location: Chennai

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

dbzTHEdinosauer · Posted: Thu Aug 21, 2008 1:16 pm

as Dick asked, please post your query......... this is thursday and it is your deadline, not ours.

Srihari Gonugunta · Posted: Thu Aug 21, 2008 2:30 pm

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;

aneeta · New User Joined: 08 Dec 2005 Posts: 48 Location: Chennai

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

dbzTHEdinosauer · Posted: Fri Aug 22, 2008 12:42 am

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.

aneeta · New User Joined: 08 Dec 2005 Posts: 48 Location: Chennai

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

Craq Giegerich · Posted: Fri Aug 22, 2008 5:47 am

This will always return the first day of the current month

dick scherrer · Posted: Sun Aug 24, 2008 8:47 am

Hi Craig,

Cool

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