This will give the last day on the month that is a Monday through Friday. This assumes the week is set to 1 for Sunday and 7 for Saturday. This code is for outputting only one year at a time.
DECLARE @dateM DATETIME, -- used to hold date to compare and output @intDW INT, -- day of week @intJ INT, -- loop control - month number @strD VARCHAR(10) -- date as string
-- Start with the first month SET @intJ = 1 -- While the month is less than 13 (Jan - Dec) WHILE @intJ < 13 BEGIN -- Create a string with the first day of month -- this could be any day within the month -- Notice that the code sets the year is 2010 SET @strD = CAST(@intJ AS VARCHAR) + '/1/2010' -- Get the last day of the month to be checked if weekday SET @dateM = DATEADD(month, DATEDIFF(month, 0, @strD) + 1, 0) - 1 -- Find the day of week SET @intDW = datepart(dw, @dateM) -- If Sunday or Saturday subtrack one from date and check again WHILE @intDW = 1 OR @intDW = 7 BEGIN SET @dateM = DATEADD(d, -1, @dateM) SET @intDW = DATEPART(dw, @dateM) END -- This is where the data can be saved if needed PRINT CONVERT(VARCHAR(10), @dateM, 101) -- Increment the month number SET @intJ = @intJ + 1 END