• If you only need compatibility back to 2005, then I guess you could call the following a "simplification".

    DECLARE @pMonth INT

    ,@pYear INT

    ,@Date DATETIME

    ;

    SELECT @pMonth = 11

    ,@pYear = 2013

    ;

    SELECT FifthWeekDay = DATEADD(dd, CASE WHEN d.DoW > 1 THEN 7 ELSE 6-d.DoW END, f.FirstOfMonth-1)

    FROM (SELECT DATEADD(mm,@pYear*12-22801+@pMonth,0)) f (FirstOfMonth)

    CROSS APPLY (SELECT DATEDIFF(dd,-1,f.FirstOfMonth)%7) d (DoW)

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)