Obtain last day of next month

aneeta · New User Joined: 08 Dec 2005 Posts: 44 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 Post subject:

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

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

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

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

He only asked for month end.

besides, it is not a db2 solution.

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

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

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

or even better. if your date is aug 31

aneeta · New User Joined: 08 Dec 2005 Posts: 44 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 Post subject:

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 Post subject:

Hello,

aneeta · New User Joined: 08 Dec 2005 Posts: 44 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 Post subject:

what columns did you find in SYSIBM.SYSDUMMY1 ?

aneeta · New User Joined: 08 Dec 2005 Posts: 44 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 Post subject:

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

Srihari Gonugunta · Active User Joined: 14 Sep 2007 Posts: 183 Location: Pune

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: 44 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