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 |
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 #485180 is a reply to message #485176] |
Mon, 06 December 2010 11:20 |
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 |
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 #485185 is a reply to message #485181] |
Mon, 06 December 2010 11:45 |
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 #485216 is a reply to message #485186] |
Mon, 06 December 2010 21:16 |
|
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 |
|
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 #485773 is a reply to message #485216] |
Thu, 09 December 2010 17:46 |
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 #485777 is a reply to message #485776] |
Thu, 09 December 2010 18:16 |
|
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 |
|
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 #622763 is a reply to message #622751] |
Fri, 29 August 2014 03:15 |
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 |
|
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 |
|
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 |
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 |
|
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.
|
|
|
Goto Forum:
Current Time: Fri Mar 29 00:57:51 CDT 2024
|