If you search the web on how to calculate the number of business days between two dates in Oracle, you would see so many results. Here is one more with some explanation to go with it. There is no magic to it. It is simple and straight-forward and this is as basic as it can get.

Let us go about doing this. More specifically, the numbering is as follows:. That was easy enough.

Different countries and regions have different holidays. It is not a good idea to hard-code the list of holidays in your query. Now, if we had a calendar table which had the holidays, just add that in to the end of the query note the highlighted part. While this works, it can certainly be tweaked a bit more.

Notice the highlighted variable substitutions and you will see that we have actually made the day generator SQL simpler and more efficient too. Based on a comment from Damodar, I have created a function that does the calculation based on a begin date and end date defined as parameters. The usage example is at the very top:.

Thanks for this nice post! Can you help me with this please?

Business Days between two dates

I am new in writing functions; sorry if there is a simple mistake in the code. Damodar, Thanks for the feed back.

Please see the last section now. I have updated the blog post to include the function that does the calculation. I have been looking for something this complete and to-the-point for days now.

Oracle – Calculating Business Days Between Two Dates (With Holiday Exclusion) – SQL Jana

Ryan, Thank you for the great feedback. Frankly, it made my day. There is nothing in there that Oracle would deprecate soon. You are commenting using your WordPress. You are commenting using your Twitter account. You are commenting using your Facebook account.

oracle11g - Calculate business days in Oracle SQL(no functions or procedure) - Stack Overflow

Notify me of new comments via email. Notify me of new posts via email. Skip to content SQL Jana Jana's ramblings on Oracle, SQL Server, PowerShell and technology.

First, we need to know all the dates in between the start and end dates. Then, we need to determine which dates among those are either a Saturday or a Sunday Finally, we count the resulting dates Let us go about doing this.

oracle - SQL to return the number of working days between 2 passed in dates - Stack Overflow

More specifically, the numbering is as follows: WHERE --Get only the dates between our start and end dates days. Need a more efficient solution? Complete solution as function: The usage example is at the very top: SQL Server — Calculating Business Days Between Two Dates With Holiday Exclusion — SQL Jana. June 5, at 2: June 5, at 4: June 6, at 3: June 13, at 2: VERY, VERY well done, thank you!

June 13, at 4: Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in: Email required Address never made public. Categories Categories Select Category. Follow SQL Jana on WordPress.