Wednesday, June 29, 2011

Remove Sunday Between To dates of a month

SELECT   (TO_DATE (:TO_DATE) - (TO_DATE (:from_date)) + 1
         )
       - (SELECT COUNT (days)
            FROM (SELECT     (  TO_DATE (TO_CHAR (TO_DATE (:from_date),
                                                  'YYYYMMDD'
                                                 ),
                                         'YYYYMMDD'
                                        )
                              + LEVEL
                              - 1
                             ) days
                        FROM DUAL
                  CONNECT BY LEVEL <=
                                TO_NUMBER (TO_CHAR (TO_DATE (:TO_DATE), 'DD')))
           WHERE TO_CHAR (days, 'DY') = 'SUN') AS total_working_days
  FROM DUAL

No comments:

Post a Comment