ScottPletcher (4/3/2014)

---

I prefer to avoid anything that is date or language setting dependent; DATEDIFF(DAY, 0, first_day_of_month) % 7 will yield a value that never changes no matter what the SQL setup is.

SELECT

DATEADD(DAY, CASE DATEDIFF(DAY, 0, first_day_of_month) % 7

WHEN datediff_mod_7_monday THEN 4 WHEN datediff_mod_7_sunday THEN 5 ELSE 6 END, first_day_of_month)

AS [5th_Business_Day_Of_Month]

FROM (

SELECT 00 AS month_increment UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL SELECT 03 UNION ALL

SELECT 04 UNION ALL SELECT 05 UNION ALL SELECT 06 UNION ALL SELECT 07 UNION ALL

SELECT 08 UNION ALL SELECT 09 UNION ALL SELECT 10 UNION ALL SELECT 11

) AS month_increments

CROSS APPLY (

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + month_increment, 0) AS first_day_of_month,

0 AS datediff_mod_7_monday, 6 AS datediff_mod_7_sunday

) AS calculated_data_1