Next business day (Monday - Friday) in Oracle?

问题:

I need to calculate the next business day (business days being Monday - Friday) in Oracle. At the moment in our reporting program we use a variable it supports <|Tomorrow|> but the problem is that customers do not receive the require details for say a Monday's delivery until a Sunday which is pointless since no one is there.

So we need to calculate Monday on the previous Friday, Tuesday on Monday, Wednesday on Tuesday, Thursday on Wednesday and Friday on Thursday.

What is the best method to do it within directly Oracle since our reporting program does not seem to have a next business day variable. Basically we need to change Where ORDER_HEADER.DELIVERY_DATE = '<|Tomorrow|>' to Where ORDER_HEADER.DELIVERY_DATE = ** next business day **

This is Oracle 10g.

Select ORDER_HEADER.DELIVERY_DATE As "Delivery Date",
  ORDER_HEADER.ORDER_NO As "Document No",
  ORDER_HEADER.CUSTOMER_ORDER_NO As "Customer Order No",
  ORDER_TOTALS.ORDER_TOTAL_QUANTITY As "Total Items",
  ORDER_TOTALS.ORDER_TOTAL_NET As "Total Net"
From ORDER_HEADER
  Inner Join ORDER_TOTALS On ORDER_HEADER.ORDER_NO = ORDER_TOTALS.ORDER_NO
Where ORDER_HEADER.DELIVERY_DATE = '<|Tomorrow|>' And ORDER_HEADER.CUSTOMER_NO = :Param1
Order By "Document No"

回答1:

These things are normally done with a table that lists all the working days in a year rather than a naive formula like:

CASE WHEN (1 + TRUNC (SYSDATE) - TRUNC (SYSDATE, 'IW')) < 5 
  THEN 1 + (1 + TRUNC (SYSDATE) - TRUNC (SYSDATE, 'IW')) 
  ELSE 1 
END --(on monday to thursday this returns 2 to 5, on fri/sat/sun this returns 1)

CASE WHEN (1 + TRUNC (SYSDATE) - TRUNC (SYSDATE, 'IW')) < 5 
  THEN TRUNC (SYSDATE) + 1 
  ELSE TRUNC (SYSDATE + 4), 'IW')  
END --(on monday to thursday this returns tomorrow's date, on fri/sat/sun it returns next monday's date)

Using a table allows you to factor in things like bank holidays, national holidays, religious festivals, voting days, mourning days, ad hoc days off that may occur for company, or political reasons etc etc. Maintaining the table then becomes an important task. Retrieving the next working day is essentially a case of querying the table for the MIN(date) WHERE date > current_date

回答2:

A typical method would be:

WHERE ( (to_char(sysdate, 'dy') in ('sun', 'mon', 'tue', 'wed', 'thu') and oh.DELIVERY_DATE = TRUNC(SYSDATE) + 1
        ) or
        (to_char(sysdate, 'dy') in ('sat') and oh.DELIVERY_DATE = TRUNC(SYSDATE) + 2
        ) or
        (to_char(sysdate, 'dy') in ('fri') and oh.DELIVERY_DATE = TRUNC(SYSDATE) + 3
        )
       )

I strongly agree with the other answer that you should have a calendar table for your business. The next business day is not only about weekends but also about holidays.

回答3:

If you looking for only week days, then one way is create your own function which returns next business day:

create or replace function calc_date(dt date)
return date as ret date;
begin
    SELECT min(dt)  into ret from (
        SELECT ( dt + 1 ) AS dt  FROM DUAL
        UNION 
        SELECT ( dt + 2 ) AS dt  FROM DUAL
        UNION 
        SELECT ( dt + 3 ) AS dt  FROM DUAL
    ) t
    where TO_CHAR(dt, 'D') not in (1,7);
    return ret;
end;

Call

select calc_date('2017-09-08') from dual;