Home » SQL & PL/SQL » SQL & PL/SQL » First business day of a given month (Oracle 10g)
First business day of a given month [message #485173] Mon, 06 December 2010 11:06 Go to next message
sidh_z
Messages: 6
Registered: November 2008
Junior Member
Hi ALL,

I need to calculate first business day of a given month . Below is complete explanation

Business day=sould not include weekends and holidays.

In a table say ACTIVITY_XX I have all the month begin dates say 01-JAN-2010,01-FEB-2010,01-MAR-2010,01-APR-2010 and so on..and I have a HOLIDAY table where all the holidays are stored.

So using the above info I need to calculate the first business day for a given month. I guess this cannot be done by using a simple SQL query? I was wondering how could it be written using a PL/SQL function.

I'll be passing the month begin date as parameter..so the function should return the first business day for that month.



Thanks a lot in advance...!!
Re: First business day of a given month [message #485175 is a reply to message #485173] Mon, 06 December 2010 11:12 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Search utility is a wonderful thing, but only if you actually use it.
Re: First business day of a given month [message #485176 is a reply to message #485173] Mon, 06 December 2010 11:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Thanks a lot in advance...!!

manually populate a table with 144 rows to cover the next 12 years.
Re: First business day of a given month [message #485177 is a reply to message #485173] Mon, 06 December 2010 11:16 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
You can take a look at the below link for help:
http://asktom.oracle.com/pls/asktom/f?p=100:11:3070538450540198::::P11_QUESTION_ID:185012348071

Regards
Ved

[Updated on: Mon, 06 December 2010 11:17]

Report message to a moderator

Re: First business day of a given month [message #485180 is a reply to message #485176] Mon, 06 December 2010 11:20 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
BlackSwan wrote on Mon, 06 December 2010 18:12

manually populate a table with 144 rows to cover the next 12 years.


I usually populate such tables AT LEAST up to my retirement age. (It probably will become the feared Y2035 bug)
Re: First business day of a given month [message #485181 is a reply to message #485175] Mon, 06 December 2010 11:23 Go to previous messageGo to next message
sidh_z
Messages: 6
Registered: November 2008
Junior Member
Thank you all..I've used the search utility and came accross this

first business day of each month:


1 declare
2 check2 date;
3 check3 varchar2(1);
4 J number(2):=0;
5 begin
6 for I in 0..11 LOOP
7 select add_months(trunc(sysdate,'YYYY'),I)
8 into check2 from dual;
9 J:=0;
10 if to_char(check2,'D') not in ('1','7') then
11 dbms_output.put_line(check2);
12 else
13 while J < 7 LOOP
14 select to_char(check2+J,'D') into check3
15 from dual;
16 J:=J+1;
17 if check3 not in ('1','7') then
18 dbms_output.put_line(check2 + check3);
19 J:=7;
20 end if;
21 end LOOP;
22 end if;
23 end loop;
24* end;


earlier but that doesn't exactly match with my requirement..

TIA..
Re: First business day of a given month [message #485184 is a reply to message #485181] Mon, 06 December 2010 11:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maybe you should try reading the whole of the thread you found that in.
Re: First business day of a given month [message #485185 is a reply to message #485181] Mon, 06 December 2010 11:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You seem to be doing a lot of work to find the first Monday of the month. How about something like this,
select next_day(trunc(sysdate,'month'),'mon') from dual;

and of course you might want to consider this:
SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> select to_char(sysdate,'D') from dual;

T
-
2

SQL> alter session set nls_territory='HUNGARY';

Session altered.

SQL> select to_char(sysdate,'D') from dual;

T
-
1


update: silly me! I misread the question. But my second point is still valid.

[Updated on: Mon, 06 December 2010 11:50]

Report message to a moderator

Re: First business day of a given month [message #485186 is a reply to message #485185] Mon, 06 December 2010 11:52 Go to previous messageGo to next message
sidh_z
Messages: 6
Registered: November 2008
Junior Member
The first business of the month may not be necessarily a Monday..like 01-DEC-2010.

TIA..
Re: First business day of a given month [message #485216 is a reply to message #485186] Mon, 06 December 2010 21:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> create table activity_xx
  2    (the_date  date)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into activity_xx
  2  select add_months (to_date ('01-JAN-2010', 'DD-MON-YYYY'), rownum - 1)
  3  from   dual
  4  connect by level <= 24
  5  /

24 rows created.

SCOTT@orcl_11gR2> select * from activity_xx order by the_date
  2  /

THE_DATE
---------
01-JAN-10
01-FEB-10
01-MAR-10
01-APR-10
01-MAY-10
01-JUN-10
01-JUL-10
01-AUG-10
01-SEP-10
01-OCT-10
01-NOV-10
01-DEC-10
01-JAN-11
01-FEB-11
01-MAR-11
01-APR-11
01-MAY-11
01-JUN-11
01-JUL-11
01-AUG-11
01-SEP-11
01-OCT-11
01-NOV-11
01-DEC-11

24 rows selected.

SCOTT@orcl_11gR2> create table holiday
  2    (hol_date  date)
  3  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into holiday values (to_date ('01-JAN-2010', 'DD-MON-YYYY'))
  3  into holiday values (to_date ('04-JUL-2010', 'DD-MON-YYYY'))
  4  into holiday values (to_date ('01-JAN-2011', 'DD-MON-YYYY'))
  5  into holiday values (to_date ('04-JUL-2011', 'DD-MON-YYYY'))
  6  select * from dual
  7  /

4 rows created.

SCOTT@orcl_11gR2> select * from holiday order by hol_date
  2  /

HOL_DATE
---------
01-JAN-10
04-JUL-10
01-JAN-11
04-JUL-11

4 rows selected.

SCOTT@orcl_11gR2> select to_char
  2  	      (decode
  3  		(to_char (nvl (hol_date + 1, the_date), 'dy'),
  4  		 'sat', nvl (hol_date + 1, the_date) + 2,
  5  		 'sun', nvl (hol_date + 1, the_date) + 1,
  6  		 nvl (hol_date + 1, the_date)),
  7  	       'DY DD-MON-YYYY')
  8  	    as first_bus_day
  9  from   activity_xx, holiday
 10  where  activity_xx.the_date = holiday.hol_date (+)
 11  order  by the_date
 12  /

FIRST_BUS_DAY
---------------------------------
MON 04-JAN-2010
MON 01-FEB-2010
MON 01-MAR-2010
THU 01-APR-2010
MON 03-MAY-2010
TUE 01-JUN-2010
THU 01-JUL-2010
MON 02-AUG-2010
WED 01-SEP-2010
FRI 01-OCT-2010
MON 01-NOV-2010
WED 01-DEC-2010
MON 03-JAN-2011
TUE 01-FEB-2011
TUE 01-MAR-2011
FRI 01-APR-2011
MON 02-MAY-2011
WED 01-JUN-2011
FRI 01-JUL-2011
MON 01-AUG-2011
THU 01-SEP-2011
MON 03-OCT-2011
TUE 01-NOV-2011
THU 01-DEC-2011

24 rows selected.

SCOTT@orcl_11gR2>

[Updated on: Thu, 09 December 2010 18:12]

Report message to a moderator

Re: First business day of a given month [message #485217 is a reply to message #485216] Mon, 06 December 2010 21:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Or without the activity_xx table:

SCOTT@orcl_11gR2> select to_char
  2  	      (decode
  3  		(to_char (nvl (hol_date + 1, the_date), 'dy'),
  4  		 'sat', nvl (hol_date + 1, the_date) + 2,
  5  		 'sun', nvl (hol_date + 1, the_date) + 1,
  6  		 nvl (hol_date + 1, the_date)),
  7  	       'DY DD-MON-YYYY')
  8  	    as first_bus_day
  9  from   (select add_months (to_date ('01-JAN-2010'), rownum - 1)
 10  		    as the_date
 11  	     from   dual
 12  	     connect by level <= 24) a,
 13  	    holiday
 14  where  a.the_date = holiday.hol_date (+)
 15  order  by the_date
 16  /

FIRST_BUS_DAY
---------------------------------
MON 04-JAN-2010
MON 01-FEB-2010
MON 01-MAR-2010
THU 01-APR-2010
MON 03-MAY-2010
TUE 01-JUN-2010
THU 01-JUL-2010
MON 02-AUG-2010
WED 01-SEP-2010
FRI 01-OCT-2010
MON 01-NOV-2010
WED 01-DEC-2010
MON 03-JAN-2011
TUE 01-FEB-2011
TUE 01-MAR-2011
FRI 01-APR-2011
MON 02-MAY-2011
WED 01-JUN-2011
FRI 01-JUL-2011
MON 01-AUG-2011
THU 01-SEP-2011
MON 03-OCT-2011
TUE 01-NOV-2011
THU 01-DEC-2011

24 rows selected.

SCOTT@orcl_11gR2>

Re: First business day of a given month [message #485772 is a reply to message #485216] Thu, 09 December 2010 17:36 Go to previous messageGo to next message
sidh_z
Messages: 6
Registered: November 2008
Junior Member
How can I write a function for this..?
Re: First business day of a given month [message #485773 is a reply to message #485216] Thu, 09 December 2010 17:46 Go to previous messageGo to next message
sidh_z
Messages: 6
Registered: November 2008
Junior Member
Hello,

I need to create a function for the same instead of a sql query..the month_begin_date will be coming from xx_activity table

so using the holiday table

how can I write a function ..?

something like

select business_day(month begin date) from dual;

business_day is the function and I'll be using business_day(month begin date) in one of my queries.

Anyone pls guide me..in a correct way.

TIA...
Re: First business day of a given month [message #485774 is a reply to message #485773] Thu, 09 December 2010 17:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>how can I write a function ..?
use any editor you know how to use.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

You should do your own homework assignments.
Re: First business day of a given month [message #485775 is a reply to message #485774] Thu, 09 December 2010 18:00 Go to previous messageGo to next message
sidh_z
Messages: 6
Registered: November 2008
Junior Member
I'm sorry for the bad explanation..I know how write a basic function but for this scenario.. I unable to write a correct one..

Sorry for the misunderstanding..!!
Re: First business day of a given month [message #485776 is a reply to message #485775] Thu, 09 December 2010 18:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Take Barbara's query, modify to return start of a specified month.
Wrap query in function.
Re: First business day of a given month [message #485777 is a reply to message #485776] Thu, 09 December 2010 18:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> create or replace function first_bus_day
  2    (p_date in date)
  3    return	  date
  4  as
  5    v_date	  date := p_date;
  6  begin
  7    select decode
  8  		(to_char (nvl (hol_date + 1, the_date), 'dy'),
  9  		 'sat', nvl (hol_date + 1, the_date) + 2,
 10  		 'sun', nvl (hol_date + 1, the_date) + 1,
 11  		 nvl (hol_date + 1, the_date))
 12    into   v_date
 13    from   (select v_date as the_date
 14  	       from   dual) a,
 15  	      holiday
 16    where  a.the_date = holiday.hol_date (+);
 17    return v_date;
 18  end first_bus_day;
 19  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> alter session set nls_date_format = 'DY DD-MON-YYYY'
  2  /

Session altered.

SCOTT@orcl_11gR2> select first_bus_day (the_date) first_bus_day
  2  from   activity_xx
  3  order  by the_date
  4  /

FIRST_BUS_DAY
---------------
MON 04-JAN-2010
MON 01-FEB-2010
MON 01-MAR-2010
THU 01-APR-2010
MON 03-MAY-2010
TUE 01-JUN-2010
THU 01-JUL-2010
MON 02-AUG-2010
WED 01-SEP-2010
FRI 01-OCT-2010
MON 01-NOV-2010
WED 01-DEC-2010
MON 03-JAN-2011
TUE 01-FEB-2011
TUE 01-MAR-2011
FRI 01-APR-2011
MON 02-MAY-2011
WED 01-JUN-2011
FRI 01-JUL-2011
MON 01-AUG-2011
THU 01-SEP-2011
MON 03-OCT-2011
TUE 01-NOV-2011
THU 01-DEC-2011

24 rows selected.

SCOTT@orcl_11gR2>

Re: First business day of a given month [message #622751 is a reply to message #485173] Fri, 29 August 2014 02:31 Go to previous messageGo to next message
canonyu
Messages: 1
Registered: August 2014
Location: Shenzhen, China
Junior Member
DECLARE 
    i_date            DATE; 
    i                 INTEGER; 
    business_day      DATE; 
    real_business_day DATE; 
    first_day         INTEGER; 
    last_day          INTEGER; 
    fist_business_day DATE; 
BEGIN 
    i_date := To_date('20140603', 'YYYYMMDD'); 

    first_day := 0; 

    last_day := 0; 

    SELECT Substr(To_char(Trunc(i_date, 'MM'), 'YYYY-MM-DD'), 9, 2) 
    INTO   first_day 
    FROM   dual; 

    SELECT Substr(To_char(Last_day (Trunc(i_date)), 'YYYY-MM-DD'), 9, 2) 
    INTO   last_day 
    FROM   dual; 

    i := last_day; 

    WHILE i >= 1 LOOP 
        BEGIN 
            SELECT Trunc(i_date, 'MM') + i - 1 
            INTO   business_day 
            FROM   dual 
            WHERE  NOT EXISTS (SELECT * 
                               FROM   your_statutory_holiday_table b 
                               WHERE  b.holiday_date = Trunc(i_date, 'MM') + i 
                                                       - 1); 

            IF To_char(business_day, 'DY') NOT IN ( 'SAT', 'SUN' ) THEN 
              real_business_day := business_day; 
            END IF; 
        EXCEPTION 
            WHEN no_data_found THEN 
              NULL; 
        END; 

        i := i - 1; 
    END LOOP; 

    fist_business_day := real_business_day; 

    dbms_output.Put_line(fist_business_day); 
END; 



Lalit : Formatted the entire code and added code tags.

[Updated on: Fri, 29 August 2014 03:08] by Moderator

Report message to a moderator

Re: First business day of a given month [message #622758 is a reply to message #622751] Fri, 29 August 2014 03:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please read and follow the forum guidelines, to enable us to help you: http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

May I know the purpose of your post? Is it a question or an attempt to reply OP who posted 3 and half years back. If you have a new question, please open a new topic. Read the above two links for help.
Re: First business day of a given month [message #622760 is a reply to message #622751] Fri, 29 August 2014 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
/forum/fa/7390/0/

What is this? A question? A solution?
What does this add from 4 years old Barbara's solution?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

[Updated on: Fri, 29 August 2014 03:11]

Report message to a moderator

Re: First business day of a given month [message #622763 is a reply to message #622751] Fri, 29 August 2014 03:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Welcome to the forum.

Please read and follow How to use [code] tags and make your code easier to read?

Also indent your code, that's hard to follow.

Also test your code before you post it - that's got typos and won't compile.

This:
first_day:=0;
 last_day:=0;
 select substr(to_char(trunc(i_date,'MM'),'YYYY-MM-DD'),9,2) into first_day from dual;
 select substr(to_char(last_day (trunc(i_date)),'YYYY-MM-DD'),9,2) into last_day from dual;

Could just be:
first_day := to_char(trunc(i_date,'MM'), 'DD');
 last_day := to_char(last_day (trunc(i_date)), 'DD');


And going through the month backwards from the end isn't exactly the most efficient way of getting the first business day.
Re: First business day of a given month [message #653315 is a reply to message #485173] Fri, 01 July 2016 09:02 Go to previous messageGo to next message
hanabi35
Messages: 1
Registered: July 2016
Location: France
Junior Member
You could try this (replace sysdate and 'from dual') :

SELECT next_day(last_day(sysdate), 
                decode(to_char(last_day(sysdate), 'DAY'), 
                       'SUNDAY', 'MONDAY', 
                       'SATURDAY', 'MONDAY',
                       'FRIDAY', 'MONDAY',
                       'MONDAY', 'TUESDAY', 
                       'TUESDAY', 'WEDNESDAY',
                       'WEDNESDAY', 'THURSDAY', 
                       'THURSDAY', 'FRIDAY', 'MONDAY')) as next_working_day_of_next_month
FROM dual;

Best regards.

Re: First business day of a given month [message #653316 is a reply to message #653315] Fri, 01 July 2016 09:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wrong:
SQL> SELECT next_day(last_day(sysdate),
  2                  decode(to_char(last_day(sysdate), 'DAY'),
  3                         'SUNDAY', 'MONDAY',
  4                         'SATURDAY', 'MONDAY',
  5                         'FRIDAY', 'MONDAY',
  6                         'MONDAY', 'TUESDAY',
  7                         'TUESDAY', 'WEDNESDAY',
  8                         'WEDNESDAY', 'THURSDAY',
  9                         'THURSDAY', 'FRIDAY', 'MONDAY')) as next_working_day_of_next_month
 10  FROM dual;
                decode(to_char(last_day(sysdate), 'DAY'),
                *
ERROR at line 2:
ORA-01846: not a valid day of the week
And this is not the question... which has been answered many years ago.

[Updated on: Fri, 01 July 2016 09:42]

Report message to a moderator

Re: First business day of a given month [message #653317 is a reply to message #653316] Fri, 01 July 2016 11:00 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I know the thread is old, but I just recently stumbled across an undocumented NLS_DATE_LANGUAGE that might be helpful in "cross-language" situations.

"numeric date language" seems to create unix-crontab compatible day of the week numbers:

SQL> select to_char(sysdate, 'DAY',
  2          'NLS_DATE_LANGUAGE=''numeric date language'''
  3         ) t
  4    from dual;

T
----
5

Re: First business day of a given month [message #653318 is a reply to message #653317] Fri, 01 July 2016 12:10 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Nice to know, it solves the problem of week day number to be independent from local territory.
SQL> select to_char(sysdate+level, 'Dy DD-MON-YYYY','NLS_DATE_LANGUAGE=''AMERICAN'''),
  2         to_char(sysdate+level,'D'),
  3         to_char(sysdate+level,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')
  4  from dual connect by level <= 7
  5  /
TO_CHAR(SYSDATE T T
--------------- - -
Sat 02-JUL-2016 7 6
Sun 03-JUL-2016 1 7
Mon 04-JUL-2016 2 1
Tue 05-JUL-2016 3 2
Wed 06-JUL-2016 4 3
Thu 07-JUL-2016 5 4
Fri 08-JUL-2016 6 5

SQL> alter session set nls_territory=jordan;

Session altered.

SQL> select to_char(sysdate+level, 'Dy DD-MON-YYYY','NLS_DATE_LANGUAGE=''AMERICAN'''),
  2         to_char(sysdate+level,'D'),
  3         to_char(sysdate+level,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')
  4  from dual connect by level <= 7
  5  /
TO_CHAR(SYSDATE T T
--------------- - -
Sat 02-JUL-2016 1 6
Sun 03-JUL-2016 2 7
Mon 04-JUL-2016 3 1
Tue 05-JUL-2016 4 2
Wed 06-JUL-2016 5 3
Thu 07-JUL-2016 6 4
Fri 08-JUL-2016 7 5
Note that in this case, to have the week day number we have to use format element 'DAY' or 'DY' but not the standard 'D':
SQL> select to_char(sysdate+level, 'Dy DD-MON-YYYY','NLS_DATE_LANGUAGE=''AMERICAN'''),
  2         to_char(sysdate+level,'D'),
  3         to_char(sysdate+level,'D','NLS_DATE_LANGUAGE=''numeric date language''')
  4  from dual connect by level <= 7
  5  /
TO_CHAR(SYSDATE T T
--------------- - -
Sat 02-JUL-2016 1 1
Sun 03-JUL-2016 2 2
Mon 04-JUL-2016 3 3
Tue 05-JUL-2016 4 4
Wed 06-JUL-2016 5 5
Thu 07-JUL-2016 6 6
Fri 08-JUL-2016 7 7
which may introduce confusion in code maintenance.

Previous Topic: How can I modify length of a column in type definition.
Next Topic: PLSQL grant script not working correctly
Goto Forum:
  


Current Time: Fri Mar 29 00:57:51 CDT 2024