Question :- How can we find first Friday and last Friday of each month in SQL Server?
Description :- This is one of the important question from SQL Server category, if you are preparing for any interview. Moreover, it is also used in majority of the IT projects. One scenario where it can be required to find the last Friday of each month is- To calculate Pay days for the employees (As, Paydays occur on the last Friday of each month). There can be numerous methods to tackle this question. But, I will be showing one very simple method to find last Friday of each month in SQL Server.
NOTE – This method can also be used to find any first or last day of a month in SQL Server (say last Sunday or last Tuesday, etc). It is very simple, you just have to customize it as per your business logic.
First and Last Friday of each Month
Answer :- Let’s calculate the first Friday and last Friday for the year 2016 in SQL Server with the following query.
--To Calculate First Friday and Last Friday of each Month of an year
-- Declare @Year parameter
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
MIN(dates) AS [First Friday],
MAX(dates) AS [Last Friday],
COUNT(dates) AS Number_of_Weeks
DATEADD(DAY, number - 1, DATEADD(YEAR, @year - 1900, 0))
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)
Below is the screenshot attached in which you can see First Friday, Last Friday and Number of Weeks in each month.
With this we reached an end to our post on “How to calculate first Friday and last Friday of each Month of an Year in SQL Server“. There are numerous ways to find the last Friday of each month. I will really appreciate those people who can add more methods as answers to this question as comments below. I hope you must have enjoyed this article and kindly write to us if you have any queries or feedback.