Friday, 8 April 2011

SQL to determine the first and last working day of the month for a given date:


SELECT MIN(DATES) FIRST_WORKING_DAY , MAX(DATES) LAST_WORKING_DAY FROM
(
    SELECT TRUNC(sysdate,'MM')+ROWNUM -1 DATES FROM
    (
    SELECT 1
    FROM Dual
    GROUP BY CUBE (2, 2, 2, 2, 2)
    )
    WHERE ROWNUM <= ADD_MONTHS(TRUNC(sysdate,'MM'),1) - TRUNC(sysdate,'MM')
)
WHERE TO_CHAR( DATES, 'DY') NOT IN ('SAT','SUN');

No comments:

Post a Comment