Home » SQL & PL/SQL » SQL & PL/SQL » Working days (Oracle 10g and Windws)
Working days [message #443741] Wed, 17 February 2010 02:54 Go to next message
aijaz786
Messages: 91
Registered: February 2010
Member
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.

I need this fucntion urgently. I will be highly thankful for all your help.

Thanks.
Re: Working days [message #443742 is a reply to message #443741] Wed, 17 February 2010 02:56 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Its a FAQ .
Please search before posting.

Oracle does not know about your official holidasy as you stated exclude official holidasy that lie between two dates.
sriram Smile

[Updated on: Wed, 17 February 2010 02:58]

Report message to a moderator

Re: Working days [message #443743 is a reply to message #443741] Wed, 17 February 2010 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fridays and saturdays can be excluded fairly easily - to exclude other holidays, you'll need a table to hold the details of these days.

The two approaches I'd consider are:
1) Use a row generator to produce a set of dates between the start and end date, and exclude the days you don't want,

2) Use an arithmetical solution which looks at the day of the week that the start and end dates are, and which derives the number of fridays and saturdays from that.

Both of these solutions would need another pass to remove dates from the holiday table.
Re: Working days [message #443744 is a reply to message #443741] Wed, 17 February 2010 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Counting the number of business days between 2 dates

Regards
Michel

[Updated on: Wed, 17 February 2010 02:59]

Report message to a moderator

Re: Working days [message #443746 is a reply to message #443744] Wed, 17 February 2010 03:06 Go to previous messageGo to next message
vibhavari
Messages: 19
Registered: October 2009
Junior Member
Hi
you can user this
SELECT TO_DATE(from_date,'MM/DD/YYYY') - 1 + ROWNUM AS d
FROM all_objects
WHERE TO_DATE(from_date ,'MM/DD/YYYY') - 1 + ROWNUM <= TO_DATE(Till_DATE , 'MM/DD/YYYY');
.. this will all the days in between..
you can exclude day having '5' for ex.
Re: Working days [message #443747 is a reply to message #443746] Wed, 17 February 2010 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALL_OBJECTS!!! You should update your knowledge, this is a Oracle 7 technique, see row generator.

Regards
Michel

[Updated on: Wed, 17 February 2010 03:09]

Report message to a moderator

Re: Working days [message #444099 is a reply to message #443741] Thu, 18 February 2010 12:39 Go to previous messageGo to next message
cristi_Buc
Messages: 12
Registered: February 2010
Location: Bucharest
Junior Member
You have to create a table for keeping the days you don't have to work, including the weekend. After that you have to create the function you need.
Re: Working days [message #444101 is a reply to message #444099] Thu, 18 February 2010 12:48 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you have not. It may be a solution.
This does not add anything to previous AskTom link.

Regards
Michel
Previous Topic: non schema user
Next Topic: how to check if primary key exists
Goto Forum:
  


Current Time: Mon Dec 05 02:54:22 CST 2016

Total time taken to generate the page: 0.08294 seconds