Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: next month end date based on input date

    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

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    values date('2009-01-31')- day('2009-01-31') days + 2 months
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Jul 2008
    Posts
    94
    similarly for April month end date

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

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    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
    Last edited by umayer; 04-22-09 at 05:36.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    This DB2 Basics: Fun with Dates and Times should be part of your bookmarks!

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •