Need to find next and previous working day in oracle

My query is somewhat like this:

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

This works fine, but I wanted to get next/previous working days (next/previous week days) instead of sysdate+1 and sysdate-1. I tried something like:

select next_day(sy开发者_如何学运维sdate, to_char(sysdate+1,'DAY')) from dual`

but cannot proceed with this :(

Please Help!!!!

---

@Tawman's answer will work, but I prefer this method for readability:

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

As everyone else has stated, this will only work to exclude weekends, not holidays.

---

Without consideration for holidays, you can use the day of the week to perform some simple date math using the DECODE function:

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

Simply substitute SYSDATE with a variable containing the date to check. The DECODE is using the day of the week to determine how many days to add or subtract from the base date.

---

To make date calculations for days of week independent from locale settings you can use truncation to beginning of ISO week which always a Monday.

Previous working day:

(
  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

Next 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

Below is full example code.

SQL Fiddle test

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

---

I think the best way to do this is use dbms_scheduler to create a schedule of all your work days. That way you can adjust it as needed and your code never has to change. Once you have the schedule created, use the dbms_scheduler.evaluate_calendar_string function to calculate the next date. This will do Monday-Friday but you could easily enhance the schedule to also remove holidays:

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; 
/ 

A bonus is you can also use it to automatically execute jobs on work days.

I just saw you also wanted to be able to do the previous workday. That's not as convenient with the schedule but can be done with a quick loop. Start a two days before today, run the schedule, and see if the result is before today. If not back up another day and do it again. Repeat until you find the previous workday.

---

This procedure allows you to get work days exclude holidays and weekends:

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;

---

to skip only weekends:

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