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