How to find Last Friday of each Month of an year in SQL Server

来源:互联网 时间:1970-01-01

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. Onescenario where it can be required to find the last Friday of eachmonth 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 tofind 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 calculatethe 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

Transact-SQL

--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 MonthSELECT MIN(dates) AS [First Friday], MAX(dates) AS [Last Friday], COUNT(dates) AS Number_of_WeeksFROM (SELECT DATEADD(DAY, number - 1, DATEADD(YEAR, @year - 1900, 0)) AS datesFROM master..spt_valuesWHERE type = 'p'AND number BETWEEN 1 ANDDATEDIFF(DAY, DATEADD(YEAR, @year - 1900, 0), DATEADD(YEAR, @year - 1900 + 1, 0))) AS tWHERE DATENAME(WEEKDAY, dates) = 'FRIDAY' -- You can put any day as per your logic hereGROUP 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.

Calculate First Friday and Last Friday of each Month of an Year in SQL Server

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.

Your Feedback hels to Improve

Rating: 10.0/ 10 (1 vote cast)

Rating: +1 (from 1 vote)

How to find Last Friday of each Month of an year in SQL Server , 10.0 out of 10 based on 1 rating



相关阅读:
Top