Zedfax Zedfax - 13 days ago 4
SQL Question

How to find next Xth of month after a date

I have two parameters :


  1. a date (Ex : 22/11/2016)

  2. a day number (Ex : 25)



I want to find the next 25th of month after 22/11/2016:
25/11/2016

Answer
select trunc(date '2016-11-22', 'month') + 25
from dual;

trunc(date '2016-11-22', 'month') will return the first of the month, the + 25 will then add the desired 25 days.

If the meaning of the second parameter depends on the "comparison" date you can do something like this:

select case 
          when extract(day from date '2016-11-22') >= 25 then 
             add_months(trunc(date '2016-11-22', 'month'), 1) + 25 
          else trunc(date '2016-11-22', 'month') + 25
        end as next_date
from dual;

Of course you would replace the hardcoded values for the date and the "number" of days with variables or column values.

This example:

with sample_data (the_date, num_days) as (
   select date '2016-11-22', 25 from dual union all
   select date ' 2016-11-26', 22 from dual union all
   select date ' 2016-11-26', 3 from dual
)
select the_date, num_days, 
       case 
          when extract(day from the_date) >= num_days then 
             add_months(trunc(the_date, 'month'), 1) + num_days - 1
          else trunc(the_date, 'month') + num_days - 1
        end as next_date
from sample_data;

will return:

THE_DATE    | NUM_DAYS | NEXT_DATE  
------------+----------+------------
2016-11-22  |       25 | 2016-11-25 
2016-11-26  |       22 | 2016-12-22 
2016-11-26  |        3 | 2016-12-03 
Comments