Oracle的下一个工作日(星期一至星期五)? [英] Next business day (Monday - Friday) in Oracle?

问题描述

我需要计算Oracle中的下一个工作日(工作日为星期一-星期五).目前，在我们的报表程序中，我们使用了一个变量，该变量支持<|Tomorrow|>，但是问题在于，直到周一为止，客户直到周一都没有收到要求的详细信息，例如星期一的交货.

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.

直接在Oracle中执行此操作的最佳方法是什么，因为我们的报告程序似乎没有下一个工作日变量.基本上我们需要将Where ORDER_HEADER.DELIVERY_DATE = '<|Tomorrow|>'更改为Where ORDER_HEADER.DELIVERY_DATE = ** next business day **

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 **

这是Oracle 10g.

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"

推荐答案

如果仅查找工作日，则一种方法是创建自己的函数，该函数将在下一个工作日返回:

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;

致电

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

这篇关于Oracle的下一个工作日(星期一至星期五)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！