Thanks for the question, trowa.

Asked: May 01, 2017 - 3:39 am UTC

Last updated: November 18, 2019 - 12:13 pm UTC

Version: sqlplus

Viewed 50K+ times! This question is

You Asked

Hi all,

I'm having trouble figuring out how to find out if the date is a weekday or a weekend from a column.

Scenario

CREATE TABLE A
(a_id VARCHAR2(20),
date_a DATE DEFAULT SYSDATE,
CONSTRAINT A_PK PRIMARY KEY (a_id));

TABLE A have some random dates

CREATE TABLE B
(b_id VARCHAR2(20),
date_desc VARCHAR2(20),
CONSTRAINT B_PK PRIMARY KEY (b_id));

Now how do I figure out if date_a is a weekday or weekend or even a holiday like for example New Year’s Day, then generate the results to date_desc of table B using PL/SQL block?

Any example is appreciated.

Thanks guys

and Connor said...

SQL> CREATE TABLE t
  2  (id int,
  3   d date);

Table created.

SQL>
SQL> insert into t
  2  select rownum, trunc(sysdate) -10 + rownum
  3  from dual
  4  connect by level <= 20;

20 rows created.

SQL>
SQL> select *
  2  from t;

  ID D
---- ---------
   1 23-APR-17
   2 24-APR-17
   3 25-APR-17
   4 26-APR-17
   5 27-APR-17
   6 28-APR-17
   7 29-APR-17
   8 30-APR-17
   9 01-MAY-17
  10 02-MAY-17
  11 03-MAY-17
  12 04-MAY-17
  13 05-MAY-17
  14 06-MAY-17
  15 07-MAY-17
  16 08-MAY-17
  17 09-MAY-17
  18 10-MAY-17
  19 11-MAY-17
  20 12-MAY-17

20 rows selected.

SQL>
SQL> select id, d,
  2   case when to_char(d,'DY') in ('SAT','SUN') then 'Weekend' end tag
  3  from t;

  ID D         TAG
---- --------- -------
   1 23-APR-17 Weekend
   2 24-APR-17
   3 25-APR-17
   4 26-APR-17
   5 27-APR-17
   6 28-APR-17
   7 29-APR-17 Weekend
   8 30-APR-17 Weekend
   9 01-MAY-17
  10 02-MAY-17
  11 03-MAY-17
  12 04-MAY-17
  13 05-MAY-17
  14 06-MAY-17 Weekend
  15 07-MAY-17 Weekend
  16 08-MAY-17
  17 09-MAY-17
  18 10-MAY-17
  19 11-MAY-17
  20 12-MAY-17

20 rows selected.

SQL>
SQL> create table pub_hols ( ph date , why varchar2(20));

Table created.

SQL>
SQL> insert into pub_hols values ( date '2017-05-01', 'May Day');

1 row created.

SQL>
SQL>
SQL> select id, d,
  2   case when to_char(d,'DY') in ('SAT','SUN') then 'Weekend' end tag,
  3   why
  4  from t,
  5       pub_hols
  6  where t.d = pub_hols.ph(+)
  7  order by 2;

  ID D         TAG     WHY
---- --------- ------- --------------------
   1 23-APR-17 Weekend
   2 24-APR-17
   3 25-APR-17
   4 26-APR-17
   5 27-APR-17
   6 28-APR-17
   7 29-APR-17 Weekend
   8 30-APR-17 Weekend
   9 01-MAY-17         May Day
  10 02-MAY-17
  11 03-MAY-17
  12 04-MAY-17
  13 05-MAY-17
  14 06-MAY-17 Weekend
  15 07-MAY-17 Weekend
  16 08-MAY-17
  17 09-MAY-17
  18 10-MAY-17
  19 11-MAY-17
  20 12-MAY-17

20 rows selected.

SQL>
SQL>

Just double check when using literals like "SUN" etc because it will depend on your nls settings.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PL/SQL weekdays and weekend

trowa barton, May 02, 2017 - 5:20 pm UTC

Amazing example, it helped me understand what to do with my own query

May 03, 2017 - 12:53 am UTC

glad we could help

doesn't work on oracle developper in 2018

A reader, March 14, 2018 - 11:19 pm UTC

March 16, 2018 - 3:56 am UTC

Um.....what do you mean ? Show us your test case

and if there more than one row with date?

A reader, May 17, 2019 - 10:38 am UTC

Thank you for the solution, is very useful.

But, if there are 20 rows with a date, and you want to figure out the five labor days of each date, what we have to do?

Because, I tried to do that, and the list contains 20^5 results

This is the query that I'm using, where 't' is a table with 20 rows

with week_dates as (
select t.record_no, t.dateweek,
trunc(t.dateweek,'IW') + (level-1) as week_date
from t
connect by level <= 5
)
select wkd.record_no, wkd.week_date, to_char(wkd.week_date, 'DY') as week_day
from week_dates wkd

Thanks in advance

May 17, 2019 - 3:54 pm UTC

Why do you have the connect by level clause? This where the extra rows are coming from.

Connect by level is a trick to generate extra rows. If T already contains the dates you want, there's no need to include this.

Just to_char the column in T, like Connor did in his example.

If you want to find the 5th working day after each date, things get a little more complicated. You may find this post helpful https://blogs.oracle.com/sql/how-to-find-the-next-business-day-and-add-or-subtract-n-working-days-with-sql

It helps but what if to exclude weekends from a range of date columns

Pran, November 14, 2019 - 4:27 pm UTC

How to exclude the weekends from a range of dates -- for ex: if we need to get the number of break days b/w start_date & end_date but need to exclude the weekends(sat&sun) among the ranges and get the count for each individual partner just for the business days in the break date range for each record

create table PARTNER_CALENDAR(partner# NUMBER,break_start_date date, break_end_date Date, break_reason NUMBER);

insert into partner_calendar values (11122,trunc(sysdate-42),trunc(sysdate-40),23);
insert into partner_calendar values (11122,trunc(sysdate-88),trunc(sysdate-80),23);
insert into partner_calendar values (11122,trunc(sysdate-75),trunc(sysdate-70),23);
insert into partner_calendar values (11122,trunc(sysdate-23),trunc(sysdate-20),23);
insert into partner_calendar values (11122,trunc(sysdate-2),trunc(sysdate+5),23);
insert into partner_calendar values (11123,trunc(sysdate-45),trunc(sysdate-41),23);
insert into partner_calendar values (11123,trunc(sysdate-78),trunc(sysdate-70),23);
insert into partner_calendar values (11123,trunc(sysdate-65),trunc(sysdate-60),23);
insert into partner_calendar values (11123,trunc(sysdate-23),trunc(sysdate-20),23);
insert into partner_calendar values (11123,trunc(sysdate-2),trunc(sysdate+5),23);

Thanks in advance

November 14, 2019 - 10:31 pm UTC

SQL> create table PARTNER_CALENDAR(partner# NUMBER,break_start_date date, break_end_date Date, break_reason NUMBER);

Table created.

SQL>
SQL> insert into partner_calendar values (11122,trunc(sysdate-42),trunc(sysdate-40),23);

1 row created.

SQL> insert into partner_calendar values (11122,trunc(sysdate-88),trunc(sysdate-80),23);

1 row created.

SQL> insert into partner_calendar values (11122,trunc(sysdate-75),trunc(sysdate-70),23);

1 row created.

SQL> insert into partner_calendar values (11122,trunc(sysdate-23),trunc(sysdate-20),23);

1 row created.

SQL> insert into partner_calendar values (11122,trunc(sysdate-2),trunc(sysdate+5),23);

1 row created.

SQL> insert into partner_calendar values (11123,trunc(sysdate-45),trunc(sysdate-41),23);

1 row created.

SQL> insert into partner_calendar values (11123,trunc(sysdate-78),trunc(sysdate-70),23);

1 row created.

SQL> insert into partner_calendar values (11123,trunc(sysdate-65),trunc(sysdate-60),23);

1 row created.

SQL> insert into partner_calendar values (11123,trunc(sysdate-23),trunc(sysdate-20),23);

1 row created.

SQL> insert into partner_calendar values (11123,trunc(sysdate-2),trunc(sysdate+5),23);

1 row created.

SQL>
SQL>
SQL> select p.*,
  2    ( select count(case when to_char(break_end_date+level-1,'D') not in ('1','7') then 1 end )
  3      from   dual
  4      connect by level <= ceil(break_end_date - break_start_date+1)
  5    ) as days
  6  from partner_calendar p
  7  /

  PARTNER# BREAK_STA BREAK_END BREAK_REASON       DAYS
---------- --------- --------- ------------ ----------
     11122 04-OCT-19 06-OCT-19           23          2
     11122 19-AUG-19 27-AUG-19           23          7
     11122 01-SEP-19 06-SEP-19           23          4
     11122 23-OCT-19 26-OCT-19           23          2
     11122 13-NOV-19 20-NOV-19           23          6
     11123 01-OCT-19 05-OCT-19           23          3
     11123 29-AUG-19 06-SEP-19           23          6
     11123 11-SEP-19 16-SEP-19           23          5
     11123 23-OCT-19 26-OCT-19           23          2
     11123 13-NOV-19 20-NOV-19           23          6

10 rows selected.

SQL>

nls independent solution

lh, November 15, 2019 - 10:55 am UTC

Hi

If one wants to use nls setting independent way, one possibility is to use day numbering of style:
select trunc(sysdate) - trunc(sysdate,'IW') + 1 from dual;

Today is friday and this will return 5.

lh

November 18, 2019 - 12:13 pm UTC

Nice touch, but you could also just use the 3rd parameter in to_char no?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

PL/SQL weekdays and weekend

doesn't work on oracle developper in 2018

and if there more than one row with date?

It helps but what if to exclude weekends from a range of date columns

nls independent solution

More to Explore

PL/SQL demos

PL/SQL docs

How to find out if the date is a weekend or weekdays

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

PL/SQL weekdays and weekend

doesn't work on oracle developper in 2018

and if there more than one row with date?

It helps but what if to exclude weekends from a range of date columns

nls independent solution

More to Explore

PL/SQL demos

PL/SQL docs