Luis Cazares (3/25/2014)

---

Assuming you have @@DATEFIRST = 7, you could use this formula:

SELECT CASE WHEN DATEPART(DW, DATEADD( MM, DATEDIFF(MM, 0, GETDATE()), 0)) = 2

THEN DATEADD( DD, 4, DATEADD( MM, DATEDIFF(MM, 0, GETDATE()), 0))

ELSE DATEADD( DD, 6, DATEADD( MM, DATEDIFF(MM, 0, GETDATE()), 0)) END

Here's a test:

SELECT CASE WHEN DATEPART(DW, DATEADD( MM, DATEDIFF(MM, 0, GETDATE()) + n, 0)) = 2

THEN DATEADD( DD, 4, DATEADD( MM, DATEDIFF(MM, 0, GETDATE()) + n, 0))

ELSE DATEADD( DD, 6, DATEADD( MM, DATEDIFF(MM, 0, GETDATE()) + n, 0)) END

FROM (VALUES(0), (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14))e(n)

It's basically testing if the first day of the month is Monday, then the 5th weekday will be the 5th day of the month, if not, it will be the 7th. You can comment the code to make it obvious or you could use the DATENAME() function instead. 😉