NOTE – This method can also be used to find any first or last day of a month in SQL Server (say lastSunday or last Tuesday, etc). It is very simple, you just have to customize it as per your business logic.
DECLARE @year int
-- Setting value for @Year parameter. You can put any year value.
SET @year = 2016
-- Now, we will calculate First and Last Friday of each Month
SELECT
MIN(dates) AS [First Friday],
MAX(dates) AS [Last Friday],
COUNT(dates) AS Number_of_Weeks
FROM (SELECT
DATEADD(DAY, number - 1, DATEADD(YEAR, @year - 1900, 0))
AS dates
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 1 AND
DATEDIFF(DAY, DATEADD(YEAR, @year - 1900, 0), DATEADD(YEAR, @year - 1900 + 1, 0))) AS t
WHERE DATENAME(WEEKDAY, dates) = 'FRIDAY' -- You can put any day as per your logic here
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, dates), 0)
