The user's question was the first step in how to account for weekends and that could have been just to add 7 days instead of each 5 days or (7*FLOOR(ndays/5)+ MOD(ndays,5).

You answered a bigger question not only to exclude weekends but holidays as well.

The code thought me of things I would never have suspected to use.
Thanks.

Tom: This is exactly what I was looking for. But could you elaborate a little more on the select statement of the procedure?

I ran this query to add 6 business days to 01/17/2003. I expect to get 8 days (because there are 2 weekend days in between). But I got nothing.

select max(rownum)
from (select rownum from all_objects)
where rownum <= 6
and to_char(to_date('01/17/2003', 'mm/dd/yyyy') + rownum, 'dy') not in ('sat', 'sun');

MAX(ROWNUM)
-----------


BTW, do you know which Oracle document list the complete built-in functions (eg max, min, length, etc.) for most recent release?

the problem with your query is all about rownum.  You have TWO different rownums in there.  


select max(rownum)   <<<<=== rownum1
  from (select rownum   <<==== rownum2
          from all_objects)
 where rownum <= 6  <<<==== rownum1
   and to_char(to_date('01/17/2003', 'mm/dd/yyyy') + rownum, 'dy') <<<=== rownum1
    not in ('sat', 'sun');

and you didn't alias the inner one so you could use it in the outer one.

Use the supplied query "as is" -- with rnum.  the answer is 10, not 8

ops$tkyte@ORA920> select max(rnum)
  2  from (select rownum rnum from all_objects)
  3  where rownum <= 6
  4  and to_char(to_date('01/17/2003', 'mm/dd/yyyy') + rnum, 'dy') not in ('sat',
  5  'sun');

 MAX(RNUM)
----------
        10

 

Hello Mr Tom,

The code that you have here goes forward from the date. I have a situation where I have to go backwards.

example: 7 business days prior to today 02/07/2003 was 29th January. Is there a way to calculate on the fly the same using something similar to what you have for going forward.

Thanks in advance

try substraction?

ops$tkyte@ORA920> !cal 1 2003
    January 2003
Su Mo Tu We Th Fr Sa
          1  2  3  4
 5  6  7  8  9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31


ops$tkyte@ORA920> !cal
    February 2003
Su Mo Tu We Th Fr Sa
                   1
 2  3  4  5  6  7  8
 9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28


ops$tkyte@ORA920> select max(rnum), sysdate, sysdate-max(rnum) six_business_days_AGO
  2    from (select rownum rnum from all_objects)
  3   where rownum <= 6
  4     and to_char(to_date('01/17/2003', 'mm/dd/yyyy') - rnum, 'dy') not in ('sat','sun')
  5  /

 MAX(RNUM) SYSDATE   SIX_BUSIN
---------- --------- ---------
         8 07-FEB-03 30-JAN-03

ops$tkyte@ORA920> select max(rnum), sysdate, sysdate+max(rnum) six_business_days_from_now
  2    from (select rownum rnum from all_objects)
  3   where rownum <= 6
  4     and to_char(to_date('01/17/2003', 'mm/dd/yyyy') + rnum, 'dy') not in ('sat','sun')
  5  /

 MAX(RNUM) SYSDATE   SIX_BUSIN
---------- --------- ---------
        10 07-FEB-03 17-FEB-03

 

select max(rnum)
from (select rownum rnum from all_objects)
where rownum <= 7
and to_char(sysdate - rnum, 'dy') not in ('sat', 'sun');

SQL> select (sysdate-max(rnum))BACKDATE
   from (select rownum rnum from all_objects)
   where rownum <= 7
    and to_char(sysdate - rnum, 'dy') not in ('sat', 'sun');

BACKDATE
---------------
29-JAN-03
 

Thanks a lot all of you for a quick response

Hi Tom,

I love your function and everything was going great until I made the change to my production environment. The function was returning weird result. After some investigation, I noticed that the function return different result

In our development server (Oracle 9.2), if the table "user_objects" is chosen the result is 545. If the table "all_objects" is chosen, the result is 7 which is correct. If this was in the production environment, it would be the reverse. Do you know why this would cause a different result?

I tested with today date (7/5/2007) and adding 5 business day.

Thanks,
Bobby


----------
CREATE FUNCTION EMS_ORACLE.ADD_BUS_DAYS(
P_DATE IN DATE
,P_ADD_NUM IN INTEGER
) RETURN DATE AS

V_CNT NUMBER;
V_BUS_DAY DATE := TRUNC(P_DATE);

BEGIN

SELECT MAX(RNUM)
INTO V_CNT
FROM (SELECT ROWNUM RNUM
FROM ALL_OBJECTS)
WHERE ROWNUM <= P_ADD_NUM
AND TO_CHAR(V_BUS_DAY + RNUM, 'DY' ) NOT IN ('SAT', 'SUN')
AND NOT EXISTS
( SELECT 1
FROM CXN_HOLIDAYS
WHERE HOLIDAY_OBSERVED = V_BUS_DAY + RNUM );
V_BUS_DAY := V_BUS_DAY + V_CNT;

RETURN V_BUS_DAY;

END ADD_BUS_DAYS;

Tom, in your last answer you used "where" instead of "connect by" in the inner query:

select level rnum from dual connect by level <= p_add_num

Regards
Michel

Hi Tom,

FYI: all_objects has 47391 records and user_objects has 666 records.

I am still getting weird result.

-----
SELECT RNUM
FROM (select level rnum from dual connect by level <= 365)
WHERE TO_CHAR(to_date('7/06/2007','mm/dd/yyyy') + RNUM, 'DY' ) NOT IN ('SAT', 'SUN')
AND NOT EXISTS
( SELECT 1
FROM CXN_HOLIDAYS
WHERE HOLIDAY_OBSERVED = to_date('7/06/2007','mm/dd/yyyy') + RNUM )
and rownum <= 5
----------

Output:

RNUM
-----
123
235
339
4
38


-----
SELECT RNUM
FROM (select level rnum from dual connect by level <= 365)
WHERE TO_CHAR(to_date('7/06/2007','mm/dd/yyyy') + RNUM, 'DY' ) NOT IN ('SAT', 'SUN')
AND NOT EXISTS
( SELECT 1
FROM CXN_HOLIDAYS
WHERE HOLIDAY_OBSERVED = to_date('7/06/2007','mm/dd/yyyy') + RNUM )
order by 1
----------

Output:

RNUM
----
3
4
5
6
7
10
11
12
13
.
.

The correct answer should be 7.

Thanks again Tom and Michel for syntax correction.

Bobby

The function is to add 5 business days from today. We disregard Saturday, Sunday and holiday. If today is "7/6/2007", the fifth business day should be "7/13/2007". I am expecting MAX(RNUM) to return a value of 7.

"7/6/2007" + 7 = "7/13/2007"

Note: For my purpose I only need a minimum sequence of up to 30 days (365 was arbitrary) since I will only add 5 business days.

Bobby, it worked out for me, (9208). I didn't use holiday's table since this is test, but looks like you have a bug in your code.

ops$marcio:LX92> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

5 rows selected.
ops$marcio:LX92> alter session set nls_date_format= 'dd/Mon/yyyy';

Session altered.

ops$marcio:LX92> var n number
ops$marcio:LX92> var dt varchar2(20)
ops$marcio:LX92>
ops$marcio:LX92> exec :n := 5

PL/SQL procedure successfully completed.


            N
-------------
            5

ops$marcio:LX92> exec :dt := '07/06/2007'

PL/SQL procedure successfully completed.


DT
--------------------------------
07/06/2007

ops$marcio:LX92>
ops$marcio:LX92>
ops$marcio:LX92> with loopin as
  2    ( select level rn from dual connect by level <= 365 )
  3  select to_date(:dt,'mm/dd/yyyy') + max(rn), max(rn)
  4    from loopin
  5   where rownum <= :n
  6     and to_char(to_date(:dt,'mm/dd/yyyy')+rn,'d') not in (1,7)
  7  /

TO_DATE(:DT       MAX(RN)
----------- -------------
13/Jul/2007             7

1 row selected.

Regards,

I notice that if I commented out the code for the holiday table it would return the correct answer.

----------------
select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> select ems_oracle.ADD_BUS_DAYS(to_date('07/06/2007', 'mm/dd/yyyy'), 5)
2 from dual;

EMS_ORACL
---------
09-JUN-08

SQL>
-------------------

CREATE FUNCTION EMS_ORACLE.ADD_BUS_DAYS(
P_DATE IN DATE
,P_ADD_NUM IN INTEGER
) RETURN DATE AS

V_CNT NUMBER;
V_BUS_DAY DATE := TRUNC(P_DATE);

BEGIN

SELECT MAX(RNUM)
INTO V_CNT
FROM (select level rnum from dual connect by level <= 365 )
WHERE ROWNUM <= P_ADD_NUM
AND TO_CHAR(V_BUS_DAY + RNUM, 'DY' ) NOT IN ('SAT', 'SUN')
AND NOT EXISTS
( SELECT 1
FROM CXN_HOLIDAYS
WHERE HOLIDAY_OBSERVED = V_BUS_DAY + RNUM );
V_BUS_DAY := V_BUS_DAY + V_CNT;

RETURN V_BUS_DAY;

END ADD_BUS_DAYS;

-------

BEGIN
   select rnum into v_cnt
     from (
   SELECT RNUM, row_number() over (order by rnum) rn
     FROM (select level rnum from dual connect by level <= 365 order by 1 )
    WHERE TO_CHAR(v_bus_day + RNUM, 'DY' ) NOT IN ('SAT', 'SUN')
      AND NOT EXISTS ( SELECT 1 FROM  CXN_HOLIDAYS WHERE HOLIDAY_OBSERVED = v_bus_day + rnum )
          )
    where rn = p_add_num;

   RETURN V_BUS_DAY+v_cnt;

END ADD_BUS_DAYS;
/

Tom, may I suggest changing

WHERE TO_CHAR(v_bus_day + RNUM, 'DY' ) NOT IN ('SAT', 'SUN')

to

WHERE TO_CHAR(v_bus_day + RNUM, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN' ) NOT IN ('SAT', 'SUN')

so it will work no matter what the NLS settings are?

and this is indepedent of the
NLS_TERRITORY setting ?

I remember some of the to_date / to_char Conversions are not
and you can't specify the NLS_TERRITORY setting as a parameter
to them, that's the main cause I don't use them at all

Reader,
NLS_DATE_LANGUAGE dictates the "Abbreviated name of day" (option 'DY'), NLS_TERRITORY dictates the first "Day of week (1-7)" (option 'D'). Therefore, the 'DY' option can be made independent of the NLS settings but the 'D' option cannot.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#62977

SQL> column "Day of week (1-7)" format A20
SQL> column "Day of week AMERICAN" format A20
SQL> column "Abbreviated name of day" format A25
SQL> column "Abbreviated AMERICAN" format A20
SQL> show parameters nls

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
...
nls_date_language                    string
...
nls_language                         string      AMERICAN
...
nls_territory                        string      AMERICA
...
SQL> with dreamliner as (select to_date('07/08/07', 'MM/DD/YY') as premiere from dual)
  2  select premiere,
  3  to_char(premiere, 'D') as "Day of week (1-7)",
  4  to_char(premiere, 'D', 'NLS_DATE_LANGUAGE=AMERICAN') as "Day of week AMERICAN",
  5  to_char(premiere, 'DY') as "Abbreviated name of day",
  6  to_char(premiere, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') as "Abbreviated AMERICAN" from dreamliner;

PREMIERE   Day of week (1-7) Day of week AMERICAN Abbreviated name of day Abbreviated AMERICAN
---------- ----------------- -------------------- ----------------------- --------------------
2007/07/08 1                 1                    SUN                     SUN

SQL> alter session set nls_date_language='FRENCH';

Session altered.

SQL> with dreamliner as (select to_date('07/08/07', 'MM/DD/YY') as premiere from dual)
  2  select premiere,
  3  to_char(premiere, 'D') as "Day of week (1-7)",
  4  to_char(premiere, 'D', 'NLS_DATE_LANGUAGE=AMERICAN') as "Day of week AMERICAN",
  5  to_char(premiere, 'DY') as "Abbreviated name of day",
  6  to_char(premiere, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') as "Abbreviated AMERICAN" from dreamliner;

PREMIERE   Day of week (1-7) Day of week AMERICAN Abbreviated name of day Abbreviated AMERICAN
---------- ----------------- -------------------- ----------------------- --------------------
2007/07/08 1                 1                    DI                      SUN

SQL> alter session set nls_territory='FRANCE';

Session altered.

SQL> with dreamliner as (select to_date('07/08/07', 'MM/DD/YY') as premiere from dual)
select premiere,
  2    3  to_char(premiere, 'D') as "Day of week (1-7)",
  4  to_char(premiere, 'D', 'NLS_DATE_LANGUAGE=AMERICAN') as "Day of week AMERICAN",
  5  to_char(premiere, 'DY') as "Abbreviated name of day",
  6  to_char(premiere, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') as "Abbreviated AMERICAN" from dreamliner;

PREMIERE Day of week (1-7) Day of week AMERICAN Abbreviated name of day Abbreviated AMERICAN
-------- ----------------- -------------------- ----------------------- --------------------
08/07/07 7                 7                    DI                      SUN

Tom,
This is just a variation on a theme....

I just thought i could use your VTABLE Pipelined function and came up with this query which could achieve the same purpose.

Any thoughts on its performance.

select DATES from
(select sysdate+column_value DATES from table(vtable) where to_char(sysdate+column_value,'DY') not in ('SAT','SUN')
and NOT EXISTS (select NULL from holidays where holiday=to_date(sysdate+column_value,'DD-MON-RR')))  where rownum<=5

with data
as
(select level l from dual connect by level < :n )
select * from data;

select MAX(DATES) from
(select sysdate+column_value DATES from table(vtable) where to_char(sysdate+column_value,'DY') not in ('SAT','SUN')
and NOT EXISTS (select NULL from holidays where holiday=to_date(sysdate+column_value,'DD-MON-RR'))) where rownum<=5

-- Need to include the MAX() function.

Hi Tom,

I have used your query to generate a list of working days in order to select rows from a table. I am currently working on 9.2i and I would like the following:
I have a table with a date and a number (working days) in it. I want to select all rows from that table where the date is smaller or equal to a supplied date minus the number of working days.

I wrote a query to do that, but it doens't always return the data I excpected. Here is the code:
-- Create the table:
CREATE TABLE times AS
SELECT TO_DATE( '01-01-2007', 'DD-MM-YYYY' ) start_date
, 2 wait_days
FROM DUAL
UNION ALL
SELECT TO_DATE( '01-01-2001', 'DD-MM-YYYY' )
, 20
FROM DUAL
UNION ALL
SELECT TO_DATE( '01-01-2004', 'DD-MM-YYYY' )
, 2
FROM DUAL;
-- The select statement:
SELECT *
FROM times t
WHERE t.start_date <=
(
SELECT trunc( TO_DATE( '20-08-2007 12:54:57', 'DD-MM-YYYY HH24:MI:SS' ) ) - rnum
FROM (
SELECT rnum
, row_number() OVER (ORDER BY rnum) rn
FROM (
SELECT level rnum
FROM dual
CONNECT BY level <= 1500
ORDER BY 1
)
WHERE to_char(trunc(TO_DATE( '20-08-2007 12:54:57', 'DD-MM-YYYY HH24:MI:SS' )) - rnum, 'DY' ) NOT IN (mvs$constant.cn_za, mvs$constant.cn_zo)
)
WHERE rn = t.wait_days
);

The result is only the rows where the wait_days equals 2, although the row with wait_days equals 20 should also be returned. When the first row in the times table has a wait_days of 20, then all rows are returned as expected.

Can you clarify the behaviour?

Thanks! Mark

Hi Tom,
My previous post contained an error. The query contained two constants. The query should be:
SELECT *
FROM times t
WHERE t.start_date <=
(
SELECT trunc( TO_DATE( '20-08-2007 12:54:57', 'DD-MM-YYYY HH24:MI:SS' ) ) - rnum
FROM (
SELECT rnum
, row_number() OVER (ORDER BY rnum) rn
FROM (
SELECT level rnum
FROM dual
CONNECT BY level <= 1500
ORDER BY 1
)
WHERE to_char(trunc(TO_DATE( '20-08-2007 12:54:57', 'DD-MM-YYYY HH24:MI:SS' )) - rnum, 'DY' ) NOT IN ('SAT', 'SUN')
)
WHERE rn = t.wait_days
);

One other thing: The select qeury is actually the where clause of an update query. But I have removed that for making the problem clear.

Hi Tom,

I hope this is the complete example.
So in short what I expect:
A list of dates from the table times, where the start_date is smaller or equal than the date I give ( august 20th 2007 ) minus the number of working days in from the table times.
So from the table below I expect to see all 3 rows and not 2. The row with the wait_days = 20 is missing.

My question: what is causing this behaviour and how to solve it without unsing the WITH statement?


--
-- Create the table times
--
CREATE TABLE times AS
SELECT TO_DATE( '01-01-2007', 'DD-MM-YYYY' ) start_date
, 2 wait_days
FROM DUAL
UNION ALL
SELECT TO_DATE( '01-01-2001', 'DD-MM-YYYY' )
, 20
FROM DUAL
UNION ALL
SELECT TO_DATE( '01-01-2004', 'DD-MM-YYYY' )
, 2
FROM DUAL;
-
-- See all rows in times
-- Result:
-- START_DAT WAIT_DAYS
--------- ----------
-- 01-JAN-07 2
-- 01-JAN-01 20
-- 01-JAN-04 2
--
SELECT *
FROM times
--
-- Do the select: This one return just two rows, while
-- I expect all 3 of them, because 20-08-2007 minus 20 working days is larger than
-- 01-01-2001 ( second row in the create table )
--
-- Result: START_DAT WAIT_DAYS
--------- ----------
-- 01-JAN-07 2
-- 01-JAN-04 2
--
SELECT *
FROM times t
WHERE t.start_date <=
(
SELECT trunc( TO_DATE( '20-08-2007 12:54:57', 'DD-MM-YYYY HH24:MI:SS' ) ) - rnum
FROM (
SELECT rnum
, row_number() OVER (ORDER BY rnum) rn
FROM (
SELECT level rnum
FROM dual
CONNECT BY level <= 1500
ORDER BY 1
)
WHERE to_char(trunc(TO_DATE( '20-08-2007 12:54:57', 'DD-MM-YYYY HH24:MI:SS' ))
- rnum, 'DY' ) NOT IN ('SAT', 'SUN')
)
WHERE rn = t.wait_days
);
--
-- Select to get the date for 20-08-2007 minus 20 working days. This return 23-07-2007 which is correct
--
-- Result:
-- TRUNC(TO_
---------
-- 23-JUL-07

SELECT trunc( TO_DATE( '20-08-2007 12:54:57', 'DD-MM-YYYY HH24:MI:SS' ) ) - rnum
FROM (
SELECT rnum
, row_number() OVER (ORDER BY rnum) rn
FROM (
SELECT level rnum
FROM dual
CONNECT BY level <= 1500
ORDER BY 1
)
WHERE to_char(trunc(TO_DATE( '20-08-2007 12:54:57', 'DD-MM-YYYY HH24:MI:SS' ))
- rnum, 'DY' ) NOT IN ('SAT', 'SUN')
)
WHERE rn = 20;
--
-- Same select as the first, to check whether the row is now returned.
--
-- Result:
-- START_DAT WAIT_DAYS
--------- ----------
-- 01-JAN-01 20
SELECT *
FROM times t
WHERE wait_days = 20
AND start_date <= TO_DATE( '23-07-2007', 'DD-MM-YYYY' );

Thanks Tom. Worked for me in first shot.Was searching for this since yesterday.

Looks funny but if you make up calendar months with the 1st starting on each day, you will see the patterns that make this possible. Add to a report you only want to return data on the 5th business day of a month:

WHERE... 'Y' = 
CASE
WHEN TRIM (TO_CHAR (SYSDATE, 'MONTH')) NOT IN
          ('JANUARY', 'JULY', 'SEPTEMBER')
      AND ( (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN ('FRIDAY')
             AND TO_CHAR (SYSDATE, 'dd') IN ('05','06','07')
           OR (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN
                                  ('MONDAY',
                                   'TUESDAY',
                                   'WEDNESDAY',
                                   'THURSDAY',
                                   'FRIDAY')
                AND TO_CHAR (SYSDATE, 'dd') IN ('07'))))
THEN 'Y'
WHEN TRIM (TO_CHAR (SYSDATE, 'MONTH')) IN ('JULY','SEPTEMBER')
     AND ( (TRIM (TO_CHAR (SYSDATE, 'DAY')) = 'MONDAY'
            AND TO_CHAR (SYSDATE, 'dd') IN ('08','09','10'))
                      OR (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN
                                ('MONDAY',
                                 'TUESDAY',
                                 'WEDNESDAY',
                                 'THURSDAY',
                                 'FRIDAY')
                        AND TO_CHAR (SYSDATE, 'dd') IN   ('08')))
THEN 'Y'
WHEN TRIM (TO_CHAR (SYSDATE, 'MONTH')) IN ('JANUARY')
              AND ( (TRIM (TO_CHAR (SYSDATE, 'DAY')) = 'MONDAY'
              AND TO_CHAR (SYSDATE, 'dd') IN ('08', '09'))
                 OR (TRIM (TO_CHAR (SYSDATE, 'DAY')) = 'FRIDAY'
              AND TO_CHAR (SYSDATE, 'dd') IN ('07'))
                OR (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN
                                ('MONDAY',
                                 'TUESDAY',
                                 'WEDNESDAY',
                                 'THURSDAY',
                                 'FRIDAY')
              AND TO_CHAR (SYSDATE, 'dd') IN ('08')))
THEN 'Y'
ELSE
'N'
END

Sorry about putting those TRIM's in there. Other efficiencies can be gained/logic combined but I wanted each section per month situation to have it be clear because it's so odd. I needed this for a user program in a product (ARGOS) where I could not use looping and scheduling options were rudimentary. Of course, I could have used a Function for the business date but in an organization it is sometimes time consuming to get tables and functions put in place and this was a fun concept.

CASE
            WHEN TO_CHAR (SYSDATE, 'MONTH') NOT IN
                       ('JANUARY', 'JULY', 'SEPTEMBER')
                 AND ( (TO_CHAR (SYSDATE, 'DAY') IN ('FRIDAY')
                        AND TO_CHAR (SYSDATE, 'dd') IN ('05', '06', '07')
                        OR (TO_CHAR (SYSDATE, 'DAY') IN
                                  ('MONDAY',
                                   'TUESDAY',
                                   'WEDNESDAY',
                                   'THURSDAY',
                                   'FRIDAY')
                            AND TO_CHAR (SYSDATE, 'dd') IN ('07'))))
            THEN
               'Y'
            WHEN TO_CHAR (SYSDATE, 'MONTH') IN ('JULY', 'SEPTEMBER')
                 AND ( (TO_CHAR (SYSDATE, 'DAY') = 'MONDAY'
                        AND TO_CHAR (SYSDATE, 'dd') IN ('08', '09', '10'))
                      OR (TO_CHAR (SYSDATE, 'DAY') IN
                                ('MONDAY',
                                 'TUESDAY',
                                 'WEDNESDAY',
                                 'THURSDAY',
                                 'FRIDAY')
                          AND TO_CHAR (SYSDATE, 'dd') IN ('08')))
            THEN
               'Y'
            WHEN TO_CHAR (SYSDATE, 'MONTH') IN ('JANUARY')
                 AND ( (TO_CHAR (SYSDATE, 'DAY') = 'MONDAY'
                        AND TO_CHAR (SYSDATE, 'dd') IN ('08', '09'))
                      OR (TO_CHAR (SYSDATE, 'DAY') = 'FRIDAY'
                          AND TO_CHAR (SYSDATE, 'dd') IN ('07'))
                      OR (TO_CHAR (SYSDATE, 'DAY') IN
                                ('MONDAY',
                                 'TUESDAY',
                                 'WEDNESDAY',
                                 'THURSDAY',
                                 'FRIDAY')
                          AND TO_CHAR (SYSDATE, 'dd') IN ('08')))
            THEN
               'Y'
            ELSE
               'N'
         END

Thanks Tom!

hi tom,
your answers are always great and very useful...

i have two columns.

date business days
2/1/2011 11
1/3/2011 4
2/11/2011 7
1/23/2011 13
1/15/2011 3
1/18/2011 25

i need to add the business days(excludes holidays, assume sat and sun are holidays) from date for eg:

1/3/2011 11
adding these it must give me 18 th of jan

what is the simplest way of writing this in a single query since for some purpose we dont want it in PL/SQL.

Is it possible to add hours instead of days using this query? I've tried using partial days but it always returns null:

select ADD_BUS_DAYS('16-NOV-13', .375) from dual

I'm trying this because I need to call this function multiple times and the end date of row 1 will become the start date for row 2, so the hours need to accumulate.

Think of a MS Project graph. In my case, one task ends and the next one begins. All I have is the original start date and the length of each task.

No need to define working hours. I just convert 8 hours into one working day, so 26 hours = 3.25 days. I just can't get the original query to accept .375 days, for example.

I do have a holiday table as well, a vendor supplied table that is horribly designed, but I switched out my query for the table into the original function above and it works fine.

I realized I asked 2 different questions with my original post. You can just ignore the second part, unless you'd like another programming challenge. I already have a solution (albeit clumsy) for the whole "end date on the first row becomes the start date for the second row" part.

My input would be a "request ID" that refers to group of tasks with a start date and a set of records that are ordered tasks that have a duration on them.

Thanks again.