在 Oracle 中查找下一个和上一个工作日

Question

我的查询有点像这样：

select 1 from dual where :p1_task_date in (sysdate,sysdate+1,sysdate-1) and :p1_task_id is not null

这工作正常，但我想获取下一个/上一个工作日（下一个/上一个工作日）而不是 sysdate+1 和 sysdate-1。我尝试过类似的操作：

select next_day(sysdate, to_char(sysdate+1,'DAY')) from dual`

但无法继续。

请帮忙。

Answer 1

@Tawman 的答案会起作用，但我更喜欢这种方法的可读性：

select sysdate as current_date,
       case when to_char(sysdate,'D') in (1,6,7)
            then next_day(sysdate,'Monday')
            else sysdate+1 end as next_weekday,
       case when to_char(sysdate,'D') in (1,2,7)
            then next_day(sysdate-7,'Friday')
            else sysdate-1 end as prev_weekday
from dual

正如其他人所说，这只能用于排除周末，而不是假期。

Answer 2

在不考虑假期的情况下，您可以使用 DECODE 函数使用星期几来执行一些简单的日期数学运算：

SELECT SYSDATE-DECODE(TO_CHAR(SYSDATE, 'D'), 2, 3, 1, 2, 1) AS WORK_DATE_BEFORE,
        TO_CHAR(SYSDATE-DECODE(TO_CHAR(SYSDATE, 'D'), 2, 3, 1, 2, 1), 'DAY') AS WORK_DAY_BEFORE,
        SYSDATE AS BASE_DATE,
        TO_CHAR(SYSDATE, 'DAY') AS BASE_DAY,
        SYSDATE+DECODE(TO_CHAR(SYSDATE, 'D'), 6, 3, 7, 2, 1) AS WORK_DATE_AFTER,
        TO_CHAR(SYSDATE+DECODE(TO_CHAR(SYSDATE, 'D'), 6, 3, 7, 2, 1), 'DAY') AS WORK_DAY_AFTER
FROM DUAL

只需将 SYSDATE 替换为包含要检查的日期的变量即可。 DECODE 使用星期几来确定从基准日期添加或减去多少天。

Answer 3

要使星期几的日期计算与区域设置无关，您可以使用 截断到ISO 周开始，其中总是星期一。

上一个工作日：

(
  case 
    when (date_value - trunc(date_value,'IW')) in (5,6,0)
      then trunc(date_value-1,'IW') + 4 
    else date_value - 1
  end
) prev_working_day

下一个工作日：

(
  case 
    when (date_value - trunc(date_value,'IW')) in (4,5,6)
      then trunc(date_value+3,'IW')
    else date_value + 1
  end
) next_working_day

下面是完整的示例代码。

SQL Fiddle 测试

with date_set as (
  select
    (trunc(sysdate) - 7 + level) as date_value
  from dual
  connect by level <= 14
),
calculated_days as (
  select 
    date_value,
    (
      case 
        when (date_value - trunc(date_value,'IW')) in (5,6,0)
          then trunc(date_value-1,'IW') + 4 
        else date_value - 1
      end
    ) prev_working_day,
    (
      case 
        when (date_value - trunc(date_value,'IW')) in (4,5,6)
          then trunc(date_value+3,'IW')
        else date_value + 1
      end
    ) next_working_day
  from 
    date_set
)
select
  date_value,
  to_char(date_value,'DAY') date_week_day,
  prev_working_day, 
  to_char(prev_working_day,'DAY') prev_day_week_day,
  next_working_day, 
  to_char(next_working_day,'DAY') next_day_week_day
from calculated_days

Answer 4

我认为最好的方法是使用 dbms_scheduler 创建所有工作日的时间表。这样您就可以根据需要进行调整，并且您的代码永远不必更改。创建计划后，使用 dbms_scheduler.evaluate_calendar_string 函数计算下一个日期。这将在周一至周五进行，但您可以轻松地增强日程安排以删除假期：

set serveroutput on 
DECLARE 
  lv_next_work_date DATE; 
BEGIN 
  dbms_scheduler.create_schedule(schedule_name=>'MY_WORKDAY_SCHEDULE', 
                                 repeat_interval=>'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI'); 
  dbms_scheduler.evaluate_calendar_string(start_date => trunc(sysdate), 
                                          calendar_string => 'MY_WORKDAY_SCHEDULE', 
                                          return_date_after => trunc(sysdate), 
                                          next_run_date => lv_next_work_date); 
  dbms_output.put_line(lv_next_work_date); 
END; 
/ 

好处是您还可以使用它在工作日自动执行作业。

我刚刚看到你也希望能够完成前一个工作日的工作。这对于时间表来说不太方便，但可以通过快速循环来完成。今天前两天开始，运行计划，看看结果是否在今天之前。如果没有备份，改天再做一次。重复此操作，直到找到前一个工作日。

Answer 5

此过程允许您获取不包括假期和周末的工作日：

create or replace procedure GetWorkDays(current_day in date default sysdate,
                                    next_date out date,
                                    prev_date out date) is
TYPE  HOLIDAY_TYPE IS VARRAY(17) OF varchar(5);
--List all holidays here
holidays HOLIDAY_TYPE := HOLIDAY_TYPE('01.01','02.01','03.01','04.01',
                                    '05.01','06.01','07.01','08.01',
                                    '23.02','08.03','01.05','02.05',
                                    '03.05','09.05','10.05','12.06',
                                    '04.11'); 
--Internal functions-------------------------------------------------
function IsHoliday(currentDay date) return number is
begin
for i in holidays.first..holidays.last
   loop
       if to_char(currentDay,'DD.MM') = holidays(i) then return 1;
       end if;
   end loop;
return 0;
end;

function GetNextWorkDay(currentDay date) return date is
tempDate Date;
begin
tempDate:=currentDay+1;
while IsHoliday(tempDate)=1 loop
   tempDate:=tempDate+1;
end loop;
if to_char(tempDate,'D') in (6,7) then
   tempDate:=next_day(tempDate,'Monday');
end if;
if IsHoliday(tempDate)=1 then return GetNextWorkDay(tempDate);
else return tempDate;
end if;
end;

function GetPrevWorkDay(currentDay date) return date is
tempDate Date;
begin
tempDate:=currentDay-1;
while IsHoliday(tempDate)=1 loop
   tempDate:=tempDate-1;
end loop;
if to_char(tempDate,'D') in (6,7) then
   tempDate:=next_day(tempDate-7,'Friday');
end if;
if IsHoliday(tempDate)=1 then return GetPrevWorkDay(tempDate);
else return tempDate;
end if;
end;
------------------------------------------------------------------
begin

next_date:=GetNextWorkDay(current_day);
prev_date:=GetPrevWorkDay(current_day);

end GetWorkDays;

Answer 6

仅跳过周末：

select 
       in_date,  
       case when next_day(in_date,'Monday')>next_day(in_date,'Friday') 
                then in_date+1 else next_day(in_date,'Monday') end next_w_day, 
       case when next_day(in_date-8,'Friday')<next_day(in_date-8,'Monday') 
                then in_date-1 else next_day(in_date-7,'Friday') end previous_w_day
from 
       (select trunc(sysdate)+rownum in_date from 
                (select * from all_objects where rownum<15))
                order by in_date