How to get working days in a year in Oracle PL-SQL

How to get working days between two dates

Following query returns the number of days in a year excluding Saturdays and Sundays.



SELECT     SUM (CASE
                   WHEN TRIM (TO_CHAR (TRUNC (SYSDATE, 'year') + LEVEL - 1,
                                       'day'
                                      )
                             ) = 'saturday'
                    OR TRIM (TO_CHAR (TRUNC (SYSDATE, 'year') + LEVEL - 1,
                                      'day'
                                     )
                            ) = 'sunday'
                      THEN '0'
                   ELSE '1'
                END
               ) COUNT
      FROM DUAL
CONNECT BY LEVEL <=
                TRUNC (TRUNC (SYSDATE, 'year') + 366, 'year')
              - TRUNC (SYSDATE, 'year')


If you want to get number of working days in any other year except the current one, you may replace the SYSDATE with any date of that particular year e.g. 01-JAN-2010.

Leave a comment for any query.


0 comments: