CODEWITHSUNDEEP
sqloracle-database
kh.tab
kh.tab

Reputation: 1304

Oracle SQL get First Business Day date of given date

How can i get the date of the first business day of a given date .

For example: 01-AUG-21 is Sunday, so the first business day is 02-AUG-21 .

Upvotes: 0

Views: 938

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6084

You can use a simple case statement in SQL or PL/SQL. In the example below, just replace SYSDATE + LEVEL with the date you would like to use.

    SELECT SYSDATE + LEVEL                                                                          AS some_date,
           TO_CHAR (SYSDATE + LEVEL, 'DY')                                                          AS some_day_of_week,
             SYSDATE
           + LEVEL
           + CASE TO_CHAR (SYSDATE + LEVEL, 'DY') WHEN 'SAT' THEN 2 WHEN 'SUN' THEN 1 ELSE 0 END AS business_day
      FROM DUAL
CONNECT BY LEVEL <= 14;

SOME_DATE    SOME_DAY_OF_WEEK    BUSINESS_DAY
____________ ___________________ ________________
02-JUL-21    FRI                 02-JUL-21
03-JUL-21    SAT                 05-JUL-21
04-JUL-21    SUN                 05-JUL-21
05-JUL-21    MON                 05-JUL-21
06-JUL-21    TUE                 06-JUL-21
07-JUL-21    WED                 07-JUL-21
08-JUL-21    THU                 08-JUL-21
09-JUL-21    FRI                 09-JUL-21
10-JUL-21    SAT                 12-JUL-21
11-JUL-21    SUN                 12-JUL-21
12-JUL-21    MON                 12-JUL-21
13-JUL-21    TUE                 13-JUL-21
14-JUL-21    WED                 14-JUL-21
15-JUL-21    THU                 15-JUL-21

Here is a PL/SQL example of the same logic

DECLARE
    FUNCTION get_business_day (p_date DATE)
        RETURN DATE
    IS
    BEGIN
        RETURN TRUNC (
                     p_date
                   + CASE TO_CHAR (p_date, 'DY')
                         WHEN 'SAT' THEN 2
                         WHEN 'SUN' THEN 1
                         ELSE 0
                     END);
    END;
BEGIN
    DBMS_OUTPUT.put_line (get_business_day (p_date => SYSDATE));
END;
/

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

Here's one option: as it is only about 1 week, create a little one-week-calendar and fetch date which is larger than the one entered as a parameter, and which isn't a weekend.

with little_calendar as
  (select to_date(:par_datum, 'dd.mm.yyyy') + level - 1 datum
   from dual
   connect by level <= 7
  )
select min(datum)
from little_calendar
where datum > to_date(:par_datum, 'dd.mm.yyyy')
  and to_char(datum, 'dy', 'nls_date_language = english') 
      not in ('sat', 'sun');

A few examples in SQL*Plus:

SQL> with little_calendar as
  2    (select to_date('&&par_datum', 'dd.mm.yyyy') + level - 1 datum
  3     from dual
  4     connect by level <= 7
  5    )
  6  select min(datum)
  7  from little_calendar
  8  where datum > to_date('&&par_datum', 'dd.mm.yyyy')
  9    and to_char(datum, 'dy', 'nls_date_language = english')
 10        not in ('sat', 'sun');
Enter value for par_datum: 01.07.2021   --> the 1st working day after 01.07.2021 (Thursday) ...

MIN(DATUM)
---------------
02.07.2021, fri                         --> ... is 02.07.2021 (Friday)

SQL> undefine par_datum
SQL> /
Enter value for par_datum: 02.07.2021   --> working day that follows 02.07.2021 (Friday) ...

MIN(DATUM)
---------------
05.07.2021, mon                         --> ... is 05.07.2021 (Monday)

SQL>

Upvotes: 0

Related Questions