Calculating the number of working days in a month with SQL

Here is a way to calculate the number of working days in a given month & year with SQL.

There is a simple way in SQL to calculate the number of working days in a given month and year. This code can also...

pick up the holidays (like the 4th July) using a HOLIDAY table and then substract them to give the actual number of working days in a particular month and year. Here it is:

                
Set feedback off echo off autop off ver off 
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY'; 
accept MONTH char format 'A3'- 
prompt'Enter MONTH for which U want the number of working days (like JAN..):' 
accept YEAR char format 'A4'- 
prompt'Enter the 4 digit Year (like 1999...):' 
select count(*) NUM_WORK_DAYS 
from ( 
select to_date( '01-&MONTH-&YEAR', 'dd-mon-yyyy' )+rownum-1 day 
from all_objects 
where rownum < to_number( to_char( last_day( '01-&MONTH-&YEAR' ),'DD' ) )+1) 
where to_number(to_char(day,'d')) between 2 and 6 
and not exists ( select NULL 
from HOLIDAY 
where HOLIDAY.actdate =day ) 
/ 

For More Information

  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip or code snippet to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
  • Ask the Experts: Our SQL, database design, Oracle, DB2, and SQL Server gurus are waiting to answer your toughest questions.

This was first published in October 2001

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close