The weekend. It’s the time that, hopefully, you get to leave your work behind. And, of course, those days that throw a spanner in your “next business day” calculations.
In this post we'll find out how to
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:
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 has several day related format masks, including:
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.
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.
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:
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:
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:
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; /
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:
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?
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.
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:
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!
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:
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.
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.
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.
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.
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!
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.
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!
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');
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.
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
UPDATED: 23 Apr 2019; fixing typos