What is the SQL query to find number of business days (excluding holidays) between 2 given dates.?

Question

List of Holidays can be maintained as a separate table.
I need a query rather than stored procedure.
I am using oracle 9i.

Answer 1

Let's say you have a table of holidays (this table needs only one field called holiday, format date). The easiest way to do what you want would be to put all holidays and all weekend days (!!) into this table (making sure that no date is in the table more than once).

Then it should work like this:

select enddate - startdate - 1 - ( select count (holiday) from holidays where holidays.holiday > startdate and holidays.holiday < enddate )

Note that in this case, the two given days (startdate and enddate) are not included themselves in the count. If they are to be included, change "- 1" to "+1" and change ">" to ">=" and "<" to "<=".

HTH/NBK