next month end date based on input date

laknar · #1 (**permalink**) 04-22-09, 02:29

how to find next month end date based on input date.

inpute date= 2009-01-31

next month end date will be 2009-02-28

rahul_s80 · #2 (**permalink**) 04-22-09, 03:14

values date('2009-01-31')- day('2009-01-31') days + 2 months

rahul_s80 · #3 (**permalink**) 04-22-09, 03:19

ignore last soln wont work always '2009-03-31'
I think you will have to use case stmt and handle feb and leap year it that

laknar · #4 (**permalink**) 04-22-09, 03:33

similarly for April month end date

it worked for feb month end and March but not for April

umayer · #5 (**permalink**) 04-22-09, 04:33

Try this:

SELECT ADD_MONTHS(LAST_DAY(your-date),1)
FROM SYSIBM.SYSDUMMY1

for example:
SELECT ADD_MONTHS(LAST_DAY(DATE('31.01.2009')),1)
FROM SYSIBM.SYSDUMMY1

stolze · #6 (**permalink**) 04-22-09, 04:44

Don't use a CASE expression - that's not necessary. You only forgot to substract another day:

Code:

$ db2 "values date('2009-01-31') - day('2009-01-31') days + 2 months - 1 day"

1
----------
02/27/2009

  1 record(s) selected.

$ db2 "values date('2008-01-31') - day('2008-01-31') days + 2 months - 1 day"

1
----------
02/28/2008

  1 record(s) selected.

tonkuma · #7 (**permalink**) 04-22-09, 05:07

Code:

------------------------------ Commands Entered ------------------------------
SELECT in_date
     , in_date - (DAY(in_date) - 1) DAYS + 2 MONTHs - 1 DAY  AS end_of_next_month
  FROM (VALUES date('2008-01-31')
             , date('2009-01-31')
             , date('2009-02-01')
             , date('2009-02-28')
             , date('2009-03-01')
             , date('2009-03-31')
             , date('2009-04-01')
             , date('2009-04-30') ) t(in_date)
;
------------------------------------------------------------------------------

IN_DATE    END_OF_NEXT_MONTH
---------- -----------------
2008-01-31 2008-02-29       
2009-01-31 2009-02-28       
2009-02-01 2009-03-31       
2009-02-28 2009-03-31       
2009-03-01 2009-04-30       
2009-03-31 2009-04-30       
2009-04-01 2009-05-31       
2009-04-30 2009-05-31       

  8 record(s) selected.

tonkuma · #8 (**permalink**) 04-22-09, 05:26

Looking into Manuals...
ADD_MONTHS and LAST_DAY are supported by DB2 for z/OS V9 and DB2 for iSeries V6R1.
But, they are not supported by DB2 for LUW V9.5.

stolze · #9 (**permalink**) 04-22-09, 05:39

Quote:

Originally Posted by tonkuma

Looking into Manuals...
ADD_MONTHS and LAST_DAY are supported by DB2 for z/OS V9 and DB2 for iSeries V6R1.
But, they are not supported by DB2 for LUW V9.5.

... and the above construct has the problem that ADD_MONTHS() is applied after LAST_DAY() so that you will get wrong results if the current month has less days than the target month.

dr_te_z · #10 (**permalink**) 04-22-09, 05:46

This DB2 Basics: Fun with Dates and Times should be part of your bookmarks!

tonkuma · #11 (**permalink**) 04-22-09, 05:56

ADD_MONTHS adjust day part of result date differently from "+ n MONTHS" labeled expression.
If day part of current date is last day of the month and the current month has less days than the target month, it will return last day of target month.

stolze · #12 (**permalink**) 04-22-09, 13:52

That's really strange in my opinion. I guess this special behavior doesn't apply to the next-to-last day in the month? I would avoid such constructs.

Internet Services

Web Development

Digital Marketing

Consumer Tech