I needed a quick and dirty way of getting the last weekday of the month.
ALTER FUNCTION [fn_LastWorkDay](
--Note assumes that Sunday = 1 and Saturday = 7
--get the last day of the month
SET @EOM = DATEADD(hh,-1,DATEADD(mm, DATEDIFF(m,0,@Date )+1, 0))
--get the day of the week
SET @DW = DATEPART(dw,@EOM)
--make sure it is not a weekend day
SELECT @EOM = CASE @DW WHEN 1 THEN DATEADD(d,-2,@EOM)
WHEN 7 THEN DATEADD(d,-1,@EOM)
ELSE @EOM END
I am confident there is a better way using nested datepart functions but this works and is simple to understand.