You can try this. There is a more efficient way by doing the same to find the first monday and then, instead of adding 1 day, add 7 days/1 week, until your month changes, but I don't have time now to work this out.

Hope it helps,

Robert

-- Declare and set the required month and year

DECLARE @Year INT

SELECT @Month = 5

SELECT @Year = 2004

-- Calculation starts here. You can move this in a function or SP

SET DATEFIRST 1 -- Set First day of week to Monday

DECLARE @Day INT

DECLARE @Counter INT

SELECT @Day = 1

SELECT @Counter = 0

-- Set up the first day of the month

SELECT @Date = CONVERT(DATETIME, CONVERT(VARCHAR(4),@Year) + RIGHT('00' + CONVERT(VARCHAR(2), @Month), 2) + RIGHT('00' + CONVERT(VARCHAR(2), @Day), 2))

WHILE Month(@Date) = @Month

  SELECT @Counter = @Counter + 1

 SELECT @Date = DATEADD(dd,1,@Date) -- Set next date)

END

SELECT @Counter AS 'Number of Mondays'