X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • SQL |
    March 2, 2018

How to Find the Next Business Day and Add or Subtract N Working Days with SQL

Chris Saxon
Developer Advocate

The weekend. It’s the time that, hopefully, you get to leave your work behind. And, of course, those days thacreate t throw a spanner in your “next business day” calculations.

Ryan McGuire / Gratisography

In this post we'll find out how to

How to Find Next Working Day

Basic date arithmetic in Oracle Database is easy. The number of days between two dates is an integer. So to get the next day, add one to your date. Or, if you’re feeling adventurous, you can use intervals.

The problem comes when the result is a weekend. In most countries the next working day after Friday is Monday. But adding one to a Friday results in a Saturday.

In this case to advance to the next working day, you need to find the current day of the week and:

  • If it’s Friday, add three to the date
  • If it’s Saturday add two to the date
  • Otherwise add one

So first you need to find the day of the week. You can extract this in Oracle Database using to_char with the “day” format mask.

Plugging this into the above logic gives a case expression like this:

       case 
         when to_char(calendar_day, 'fmday') = 'friday' then 
           calendar_day + 3
         when to_char(calendar_day, 'fmday') = 'saturday' then 
           calendar_day + 2
         else
           calendar_day + 1
       end next_day

If weekends fall on different days in your country, update the logic above to skip the days as needed.

TO_CHAR Day Formats

To_char has several day related format masks, including:

  • DAY – Returns the name of the day
  • DY – Gives the three letter abbreviation of the day name
  • D – returns the day of week as a number 1-7

You control the case of the output by the case of the input.

select to_char(date'2018-01-01', 'Day') initcap,
       to_char(date'2018-01-01', 'day') lower,
       to_char(date'2018-01-01', 'DAY') upper
from   dual;

INITCAP     LOWER       UPPER       
Monday      monday      MONDAY  

Also note that without the FM prefix, the string is right-padded with spaces to match the length of the longest day.

Obviously the values returned by the name of day formats depend on the language. This is determined by the client’s setting for the NLS_DATE_LANGUAGE parameter. So servers in different parts of the world can return different values.

For example:

alter session set nls_date_language = SPANISH;
select to_char(date'2018-01-01', 'Day') dy
from   dual;

DY          
Lunes  

alter session set nls_date_language = ENGLISH;
select to_char(date'2018-01-01', 'Day') dy
from   dual;

DY          
Monday  

You may think you can avoid this using to_char(<dt>, ‘d’), which returns the day number of the week.

But this varies by NLS_TERRITORY!

For some reason, the US believes Sunday is the first day of the week. Whereas most of the rest of the world and the ISO standard consider Monday the start. So for Monday the output could flip between 1 and 2:

alter session set nls_territory = AMERICA;
select to_char(date'2018-01-01', 'd') day_number
from   dual;

DAY_NUMBER   
2 

alter session set nls_territory = "UNITED KINGDOM";
select to_char(date'2018-01-01', 'd') day_number
from   dual;

DAY_NUMBER   
1 

This could cause you all sorts of headaches as your application conquers the globe.

Luckily you can avoid these problems by fixing the language for the conversion. Do this by passing the desired language in the third parameter of to_char:

alter session set nls_date_language = ENGLISH;
select to_char(date'2018-01-01', 'fmDay', 'NLS_DATE_LANGUAGE = SPANISH') day_name
from   dual;

DAY_NAME   
Lunes  

You can only do this with the language, not the territory. So you’re best off avoiding the ‘D’ format in your code.

How to Add N Business Days

The logic above works well enough when you want the next day. But sometimes you need to add a variable number of working days to a date, such as five.

In this case you need to think carefully. If you want to add one working day and it’s currently Saturday, there are two ways to do the calculation:

  • Add one day to the date. If the result is a non-working day, move to the next date. The resulting date is a Monday.
  • Move to the next working day. Then add one working day. The resulting date is a Tuesday!

As my Twitter poll shows, people are evenly split in which approach they take:

It also brings up the interesting question of what happens when you add zero days to a non-working day.

Mathematically, adding zero is the identity operation. i.e. you get what you started with.

But if you’re adding working days, shouldn’t the result always be a working day?!

There’s no fixed answer to this. For the calculations below I’m assuming that:

  • Adding zero returns the current date, whether it’s a working day or not
  • To add N working days, you first move to the next working day. Then add the days.

Whatever you do, make sure you confirm these assumptions with your business! If they have a different understanding of this you could end up with lots of off-by-one errors. Which could lead to lots of unhappy people if these calculations relate to payment dates…

OK, so we’ve got that out of the way. How do you to the calculation?

At first glance, if the input date is a weekend you may be tempted to bump it to the next Monday. Then add the days. But this only works if N is less than a working week. As soon as it spans a weekend, it falls apart. You need to bypass an unknown number of non-working days.

The simple method to do this is:

  • Start a loop, beginning with the initial date. If this is a weekend, move it to the next Monday
  • On each iteration increment the date
  • If the date is a weekday, also increment a counter
  • Stop the loop when the counter equals the requested number of days

As you’re looping until a condition is true, a while loop is a great candidate. So you can build a function like this:

create or replace function add_n_working_days ( 
  start_date date, working_days pls_integer
) return date as
  end_date date := start_date;
  counter  pls_integer := 0;
begin

  if working_days = 0 then
    end_date := start_date;
  elsif to_char(start_date, 'fmdy') in ('sat', 'sun') then
    end_date := next_day(start_date, 'monday');
  end if;
  
  while (counter < working_days) loop
    end_date := end_date + 1;
    if to_char(end_date, 'fmdy') not in ('sat', 'sun') then
      counter := counter + 1;
    end if;
  end loop;
  
  return end_date;
  
end add_n_working_days;
/

At this point you may be worried. Loops are slow, right? Where’s the set-based SQL goodness we’re always banging on about?!

True, as you increase N the function will get proportionally slower:

But, before you get scared, the scale is in microseconds. So even if you’re adding hundreds of days it’ll still complete before you blink.

But in some cases every CPU cycle counts, right?

Luckily you can take shortcuts.

Say you’re adding a hundred working days. You don’t need to loop 100 times plus however many weekends the range spans. Instead you can add the number of weeks you’re spanning. Then loop over the last few days to get the result.

So if the timeframe is longer than a working week (five days) you can:

  • Find the number of weeks by taking the floor of the N / 5
  • Multiply it by seven to give the number of weeks spanned
  • Move the start date to the next Monday, then add the calculated weeks
  • Loop from this point for another ( N mod 5 ) days

Which in PL/SQL looks like:

create or replace function add_n_working_days_optimized ( 
  start_date date, working_days pls_integer
) return date as
  end_date       date;
  counter        pls_integer := 0;
  remaining_days pls_integer;
  weeks          pls_integer;
begin
  
  if working_days = 0 then
    end_date := start_date;
  elsif to_char(start_date, 'fmdy') in ('sat', 'sun') then
    end_date := next_day(start_date, 'monday');
  else
    end_date := start_date;
  end if;
  
  if working_days <= 5 then
    remaining_days := working_days;
  else
    weeks := floor ( working_days / 5 ) ;
    end_date := end_date + ( weeks * 7 );
    remaining_days := mod ( working_days, 5 ) ;
  end if;
  
  while (counter < remaining_days) loop
    end_date := end_date + 1; 
    if to_char(end_date, 'fmdy') not in ('sat', 'sun') then
      counter := counter + 1;
    end if;
  end loop;
  return end_date;
end add_n_working_days_optimized;
/

So now, worst case, you loop through five days.

This is great. But what if you want to subtract N days, going back in time?

The good news is this all works in reverse.

Subtracting is the same as adding a negative amount. So by adding the weeks multiplied by the sign of N, you decrement the start date. Then loop until the working day counter hits the absolute value of the remaining days.

create or replace function add_n_working_days_optimized ( 
  start_date date, working_days pls_integer
) return date as
  end_date       date;
  counter        pls_integer := 0;
  remaining_days pls_integer;
  weeks          pls_integer;
begin
  
  if working_days = 0 then
    end_date := start_date;
  elsif to_char(start_date, 'fmdy') in ('sat', 'sun') then
    if sign(working_days) = 1 then 
      end_date := next_day(start_date, 'monday');
    else
      end_date := next_day(start_date-7, 'friday');
    end if;
  else
    end_date := start_date;
  end if;
  
  if abs(working_days) <= 5 then
    remaining_days := working_days;
  else
    weeks := floor ( abs(working_days) / 5 ) * sign(working_days);
    end_date := end_date + ( weeks * 7 );
    remaining_days := mod ( working_days, 5 );
  end if;
  
  while (counter < abs(remaining_days)) loop
    end_date := end_date + sign(working_days);
    if to_char(end_date, 'fmdy') not in ('sat', 'sun') then
      counter := counter + 1;
    end if;
  end loop;

  return end_date;

end add_n_working_days_optimized;
/

So now we can find the next working day after any date. But what if you want to count the working days in a date range?

How to Find the Number of Working Days Between Two Dates

When figuring out whether you can deliver your code by the ludicrously optimistic perfectly reasonable release date your manager you gave you, it’s useful to know how many working days there are between now and then.

Ryan McGuire / Gratisography

So how do you do this?

Again, you have to be careful. Do you include the end date or not?

In any case, there’s a simple solution available:

  • Start a loop from one up to the number of days between the start and end dates. Minus one if you exclude the end date.
  • On each iteration, add the current index to the start date
  • If the result is a weekday, increment a counter of working days

Which in code looks like:

create or replace function get_working_days_between ( 
  start_date date, end_date date
) return pls_integer as
  counter  pls_integer := 0;
  date_range pls_integer;
begin

  date_range := end_date - start_date;
  
  for dys in 1 .. abs( date_range ) loop
    if to_char (
      start_date + ( dys * sign ( date_range ) ), 'fmdy'
    ) not in ('sat', 'sun') then
      counter := counter + 1;
    end if;
  end loop;
  
  return counter;
  
end get_working_days_between;
/

Hmmm. More loops. It’s going to be slow if the dates are far apart, right?

Well, yes, it will take longer.

But there’s a bigger problem looming. And at last we’re going to see some SQL!

Public Holidays

Dates are funny things. A working day isn’t a fixed concept. Different businesses and different countries assign it in different ways.

Many businesses consider public holidays to be non-working days. Which brings a couple of problems.

First up, for the most part they’re not fixed days. The date of Easter changes every year.

And even those that fall on a fixed date, such as Christmas day, often have the public holiday assigned to the next weekday when they fall on a weekend.

To handle these cases you can use logic like:

  • Find the next working day
  • If this is a public holiday, skip to the next workday

To do this you’ve got to go with the basic loop. The optimized method adding weeks will incorrectly skip holidays. So you need to extend the non-working day test to include these dates, like so:

if to_char(end_date, 'fmdy') not in ('sat', 'sun') and
   end_date not in (
     date'2018-01-01', date'2018-03-30', date'2018-04-01',
     date'2018-05-07', date'2018-05-28', date'2018-08-07',
     date'2018-12-25', date'2018-12-26'
   ) then
  counter := counter + 1;
end if;

Filling this out for all non-working days is hugely messy. Particularly if you want to include every holiday for the foreseeable future. And even that doesn’t remove all code maintenance. If the Queen fancies a day off after working for sixty years, a new holiday appears.

And the problem gets worse if you need to handle holidays for different countries.

Luckily, there’s a solution:

Make it data-driven.

Why Every Application Needs a Dates Table

Instead of programmatically figuring out if each is a working day or not, store the results in a table!

Create a table with a row for each day for the foreseeable future:

create table calendar_dates (
  calendar_day   date not null primary key,
  is_working_day varchar2(1) not null
);

Oracle Database doesn't have a SQL Boolean data type. So is_working_day column stores Y/N, 1/0 or however you want to represent true/false in your database. You can then use it bypass non-working days as needed.

This allows you to give control over what counts as a working day or not to whoever in the business makes these decisions. All you need is a simple admin app where staff can set the non-working days.

Then, with a bit of SQL, your code will adapt as the data changes. Without you needing to write any more code!

But before we get to that, a quick note on dates in Oracle Database.

Day Data Type in Oracle Database

Sadly there’s no “day with no time” data type in Oracle Database. Dates and timestamps include times. Always.

So the convention for calendar days is to store them as dates with a time of midnight. To ensure you can only store midnight, you need a check constraint:

alter table calendar_dates add constraint is_midnight 
  check ( calendar_day = trunc ( calendar_day ) );

By default, trunc() returns a date at midnight. So this check will fail if you try and pass a date at a time other than 00:00:00.

With this in place we can - at last! – get to some SQL.

Find Next Working Day with SQL

The first thing you need to do is restrict the data set to working days on or after the start date.

At this point you’ve only got working days. So you want the next row in the set, ordered by calendar date. The analytic function lead does this for you.

This will find the next day for every row. You only want the first of these, which you can get with min:

with dates as (
  select * from calendar_dates
  where  calendar_day >= to_date(:start_date, 'dd/mm/yyyy')
  and    is_working_day = 'Y'
), plus_n_days as (
  select lead(calendar_day) 
           over (order by calendar_day) dt
  from   dates
)
  select min(dt) from plus_n_days;

At this point it’s worth noting: this will process all rows in the table after your initial date. To rely on this method, you’ll need at least six months to a year’s worth of days after the current date. Possibly more. And the SQL will access them all!

That’s a lot of wasted effort.

To limit this you need an upper bound for the end date. Which is the date you’re looking for!

So you’re in a chicken-and-egg situation.

As a clumsy hack elegant workaround, you can use an upper bound of the start date plus the largest number of consecutive non-working days. Plus one.

In the UK Good Friday and Easter Monday are both public holidays. So you can easily have a run of four non-working days. So the next working day could be as many as five after the supplied date.

To restrict the data and improve performance, only access the working days between the requested date plus five days:

where  calendar_day between to_date(:start_date, 'dd/mm/yyyy')
                    and to_date(:start_date, 'dd/mm/yyyy') + 5

Or, if you’re risk-averse, six days. Or, even better, make this fudge factor safety margin data driven too!

Find the Nth Working Day with SQL

You can use this as the basis for adding N working days to a date. First, remember that adding one working day may not be the same as getting the next working day. Sticking with “move to next working day, then add N” approach, change the where clause above to only select working days on or after the supplied date.

Then, to look N rows ahead, use the second parameter of lead! This states how many rows forward to inspect.

Finally, to aid performance, you’ve got to consider how to define an upper bound for the end date.

First you need to cover the complete calendar weeks N spans. So convert working weeks to this by dividing N by five working days and multiplying by seven calendar days.

Then you need an extra buffer on top of this to account for public holidays or any shutdowns your business may have. Speak with your users to find what the longest run of non-working days your business expects.

Altogether this gives:

with dates as (
  select * from calendar_dates
  where  calendar_day between to_date(:start_date, 'dd-mm-yyyy')
                      and to_date(:start_date, 'dd-mm-yyyy') + 
                            ( :working_days / 5 * 7 ) + 10
  and    is_working_day = 'Y' 
), plus_n_days as (
  select lead(calendar_day, :working_days) 
           over (order by calendar_day) dt
  from   dates
)
  select min(dt) from plus_n_days;

Of course, using an upper bound like this makes me uneasy.

What if you set it too low, leading to incorrect results?

To avoid this, you can pick large buffer such as 50. But do this and you’re losing the performance benefit of culling unnecessary rows.

Luckily there’s another way: a cursor for loop

This is like the original looped solution. But now we’re data-driven. And it’s easy to write too:

  for dates in (
    select * from calendar_dates
    where  calendar_day > start_date
    and    is_working_day = 'Y'
    order  by calendar_day
  ) loop
    
    end_date := dates.calendar_day;
    counter := counter + 1;
    
    exit when counter > working_days;
    
  end loop;

But loops are slow, right?

True, but there’s a great way to speed up SQL loops: bulk collection. Load all the data into an array, then process that.

So how does the performance of these SQL methods compare?

To find out, I loaded calendar_dates with twenty years' worth of rows. Ten in the past, ten in the future. Then tested the various SQL methods for increasing numbers of working days, as in this LiveSQL script.

Here's the results for increasing values of N:

Clearly the unbounded SQL method is by far the slowest. Even worse it takes more-or-less the same time, however many days you're adding. Luckily, the optimized version capping the maximum number of days you inspect is the fastest. As long as you can define a safety margin large enough to cover the longest run of consecutive holidays you're fine. 

If you can't, the bulk approach is best. My tests had a collection limit of 100. If you normally expect add few days, you could squeeze more performance out of this by using a smaller collection limit. 

In any case, you need to ensure your dates table has enough days in the future for the working days to add!

To stop you running out of dates, create a job. Set it to run every week or month it and insert the next batch of dates.

How to Subtract N Working Days in SQL

The previous methods work great if you want to add N days. But what if you want to subtract instead?

You've got to work backwards through the dates. So you need to flip the operators around. Swap greater than for less than and sort descending.

So the cursor for looping through the data becomes:

select * from calendar_dates
where  calendar_day < to_date(:start_date, 'dd/mm/yyyy')
and    is_working_day = 'Y'
order  by calendar_day desc;

And for the set method you need to make more changes. Lead only accepts positive values for rows to jump ahead. Change it to lag to look back and get the max value this returns

Altogether this gives a SQL statement like:

with dates as (
  select * from calendar_dates
  where  calendar_day between to_date(:start_date, 'dd-mm-yyyy') +
                                ( :working_days / 5 * 7 ) - 10
                      and to_date(:start_date, 'dd-mm-yyyy')
  and    is_working_day = 'Y'
), minus_n_days as (
  select lag(calendar_day, abs(:working_days)) 
           over (order by calendar_day) dt
  from   dates
)
  select max(dt) from minus_n_days

Now change your add N function so it picks the appropriate query based on whether N is positive or negative.

One final note. 

If you want these pure SQL methods to include the current date in the calculation (so adding one working day to Saturday returns Monday instead of Tuesday) change the where clause to:

where ( 
  is_working_day = 'Y' or     
  calendar_day = to_date(:start_date, 'dd/mm/yyyy')
)

Phew! We got there at last!

Count Working Days Between Two Dates with SQL

Now we have a dates table, the great news is counting business days is simple. Select the count of working days after the start date, up to the end date. If you're excluding the end date, make this strictly less than otherwise less than or equal to:

select count(*)
from   calendar_dates
where  is_working_day = 'Y'
and    calendar_day > to_date(:start_date, 'dd-mm-yyyy')
and    calendar_day <= to_date(:end_date, 'dd-mm-yyyy');

Easy!

Armed with this knowledge, you write your next business day functions.

Then the CEO announces the company is launching operations all around the EU. Now you need to support holidays in many countries. Aaaaargh!

The best method is to extend the table to handle holidays in different countries. You may be tempted to do this by adding a flag for each country you need to support. For example:

create table calendar_dates (
  calendar_day      date not null primary key,
  is_working_day_gb varchar2(1) not null,
  is_working_day_fr varchar2(1) not null,
  is_working_day_de varchar2(1) not null,
  is_working_day_es varchar2(1) not null,
  -- and so on for every other country you support...
  constraint is_midnight check ( calendar_day = trunc ( calendar_day ) )
);

This can work if you know you only need to support a small number of countries. But to choose dates for a given country, either you need to use dynamic SQL, like this:

begin

  stmt := 'select count(*)
from   calendar_dates
where  is_working_day_' || country_code || q'| = 'Y'
and    calendar_day between :start_date and :end_date|';

  execute immediate stmt 
    into  working_days 
    using start_date, end_date ;
    
  return working_days;
  
end;

Or come up with a big case expression like this:

select count(*)
from   calendar_dates
where  case
         when :country_code = 'GB' then
           is_working_day_gb
         when :country_code = 'FR' then
           is_working_day_fr
         when :country_code = 'DE' then
           is_working_day_de
         when :country_code = 'ES' then
           is_working_day_es
         ...
       end = 'Y'
and    calendar_day between :start_date and :end_date

Neither of these is particularly appealing. And if you need to support a new country, you still need to run an alter table to add it. And update any code which maintains this.

A better option is to make the country a column in the working dates table.

For example:

create table country_working_calendar_dates_iot (
  country_code   varchar2(2) not null,
  calendar_day   date not null,
  is_working_day varchar2(1) not null,
  primary key ( country_code, calendar_day )
  constraint is_midnight check ( calendar_day = trunc ( calendar_day ) )
) organization index;

If you go down this route you’ll always query by country. So to aid performance I recommend making this an index-organized table with country as the leading column of the primary key.

To use this, extend the examples above to accept the country too. And everything is fully data driven!

Expanding into Mongolia?

Your admin team can add the data with that lovely app you built!

So you can focus on delivering more functionality. Or enjoying some work-free weekends ;)


Over to you!

How do you handle next working day calculations in your application? Any other options you prefer to these?

Let us know in the comments!

UPDATE 26 Feb 2019: Removing duplicate create in calendar_dates creation

Join the discussion

Comments ( 10 )
  • Rajeshwaran, Jeyabal Monday, March 5, 2018
    ....
    True, as you increase N the function will get proportionally slower:
    ....

    Perhaps that would be the time to think about the SQL Model clause.

    since the plsql function requires pre-compilation, where as with SQL that's not the case.

    demo@ORA11G> variable dt varchar2(20)
    demo@ORA11G> variable n number
    demo@ORA11G> exec :dt := '31-dec-2017'; :n :=5;

    PL/SQL procedure successfully completed.

    demo@ORA11G> exec dbms_output.put_line( add_n_working_days( to_date(:dt,'dd-mon-yyyy'), :n) );
    08-JAN-2018

    PL/SQL procedure successfully completed.

    demo@ORA11G> select end_dt
    2 from (
    3 select to_date(:dt,'dd-mon-yyyy') start_dt ,
    4 :n working_days
    5 from dual
    6 )
    7 model
    8 dimension by (1 x)
    9 measures( start_dt, working_days, 1 counter, cast(null as date) end_dt )
    10 rules iterate(10000) until ( iteration_number >= working_days[1] )
    11 ( end_dt[iteration_number=0] = case when working_days[1] = 0 then start_dt[1]
    12 when to_char( start_dt[1] ,'fmdy') in ('sat','sun') then
    13 next_day( start_dt[1] ,'monday') end ,
    14 end_dt[iteration_number>0] order by iteration_number =
    15 case when to_char( end_dt[1]+1,'fmdy') not in ('sat','sun') then
    16 end_dt[1]+1
    17 else next_day( end_dt[1]+1 ,'monday') end )
    18 /

    END_DT
    -----------
    08-JAN-2018

    demo@ORA11G>

    PS: not tested for "all" the boundary cases.
  • Chris Saxon Monday, March 5, 2018
    You're obsessed with the model clause!

    If for some reason you need to avoid compiling PL/SQL, you could run the optimized version as an anonymous block...
  • lh Monday, April 30, 2018
    Hi

    nls independent way of finding out daynumber on the week is

    trunc(sysdate) - trunc(sysdate,'iw') +1


    1=monday
    2=tuesday
    ..
    7=sunday


    lh
  • Chris Saxon Monday, April 30, 2018
    Yep, that's one way to get consistent day-of-week numbering.

    Thanks for sharing.
  • Charlie Callaway Wednesday, May 2, 2018
    Fantastic post. Thank you Chris!
  • Chris Saxon Wednesday, May 2, 2018
    Thanks Charlie, glad you found it useful!
  • John Rhodes Friday, August 10, 2018
    Here is a way to get the next day without using a loop. It uses the fact that 2nd Jan 0001 was a Sunday. It derives the day of the week of date d, and then uses the mod function to add either 0, 1 (if current Date is Saturday) or 2 (if current date is Friday) days.

    select d currentDate,
    to_char(d,'Day') currentDay,
    d+1+mod(least(8-(mod(d - to_date('02-01-0001','DD-MM-YYYY'),7)+1),3),3) nextDate,
    to_char(d+1+mod(least(8-(mod(d - to_date('02-01-0001','DD-MM-YYYY'),7)+1),3),3),'Day') nextDay
    from
    (select trunc(sysdate) d from dual
    union select trunc(sysdate)+1 d from dual
    union select trunc(sysdate)+2 d from dual
    union select trunc(sysdate)+3 d from dual
    union select trunc(sysdate)+4 d from dual
    union select trunc(sysdate)+5 d from dual
    union select trunc(sysdate)+6 d from dual);
  • John Rhodes Friday, August 10, 2018
    For adding n days:

    select
    d1
    ,x
    ,d1+ ( mod(least(8-(mod(mod(d1- to_date('02-01-0001','DD-MM-YYYY'),7),7)+1),3),3)) + mod((x-1),5) + floor((x-1)/5)*7 + 1
    from
    (
    select trunc(sysdate) d1,1 x from dual
    union select trunc(sysdate) d1,2 x from dual
    union select trunc(sysdate) d1,3 x from dual
    union select trunc(sysdate) d1,4 x from dual
    union select trunc(sysdate) d1,5 x from dual
    union select trunc(sysdate) d1,6 x from dual
    union select trunc(sysdate) d1,7 x from dual
    union select trunc(sysdate) d1,8 x from dual
    union select trunc(sysdate) d1,9 x from dual
    union select trunc(sysdate) d1,10 x from dual
    union select trunc(sysdate) d1,11 x from dual
    union select trunc(sysdate) d1,12 x from dual
    union select trunc(sysdate) d1,13 x from dual
    union select trunc(sysdate) d1,14 x from dual
    )
  • Chris Saxon Friday, August 10, 2018
    Thanks, that's some impressive date manipulation John!

    Care to give us an explanation of how it works?
  • John Rhodes Monday, August 13, 2018
    My SQL above had flaw.
    Here is some plsql that finds the nth working day in the future - hopefully inline comments are sufficiently explanatory.


    declare
    -- When looking for the nth working day, it can be split into the number of whole weeks
    -- and the number of remaining days
    --
    -- The number of remaining days will be 5 or less and the patter of days that these 5 days days will fall in is included in the YYYYYNNYYYYY string
    dayStr varchar2(30) := 'YYYYYNNYYYYY';
    --
    -- The reference date is 03-01-0001, which was a Monday
    day0 date := to_date('03-01-0001','DD-MM-YYYY'); -- Monday
    --
    -- Inputs
    dayInput date := trunc(sysdate)+5;
    numberOfDays number := 4;
    --
    dayOfWeek number;
    numberOfWholeWeeks number;
    remainingWorkingDays number;
    --
    newDayStr varchar2(30);
    lastWorkingDay number;
    lastWorkingDate date;
    --
    begin
    -- Derive the day of the week of the input day (MON=1, SUN=7)
    dayOfWeek := mod((dayInput - day0),7)+1;
    -- Derive the number of whole weeks in the numberOfDays
    numberOfWholeWeeks := floor(numberOfDays/7);
    -- Derive the number of remaining days after taking off the number of whole weeks
    remainingWorkingDays := mod(numberOfDays-1,5)+1;
    --
    dbms_output.put_line('dayOfWeek = ' || dayOfWeek);
    dbms_output.put_line('numberOfWholeWeeks = ' || numberOfWholeWeeks);
    dbms_output.put_line('remainingWorkingDays = ' || remainingWorkingDays);
    --
    -- modify the day Pattern to start on dayOfWeek+1
    newDayStr := substr(dayStr,dayOfWeek+1);
    dbms_output.put_line('newDayStr = ' || newDayStr);
    --
    -- Find the position of nth working day (Y) in the newDayStr - where n = remainingWorkingDays
    lastWorkingDay := instr(newDayStr,'Y',1,remainingWorkingDays);
    dbms_output.put_line('lastWorkingDay = ' || lastWorkingDay);
    --
    -- Calculate the nth working day after the start date
    lastWorkingDate := dayInput + numberOfWholeWeeks*7 + lastWorkingDay;
    dbms_output.put_line('lastWorkingDate = ' || lastWorkingDate);
    --
    end;
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services