Get The First And Last Business Day Of The Previous Month

Question

Hello everyone. I am trying to find a simple (if possible) way to get the first and last business days for the previous month using SSMS. For example, it is now August of 2022, so the first and last business days for July would be July 1 and July 29. I would then be passing each of these dates to a begin date and end date variable to be used with other code. Is what I am trying to achieve possible? Thank you

Answer 1

As Erland said, I would suggest you use a calendar table to calculated it one time for the last +-100 years and never think about it again.

This is another calendar table implementation:

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

Answer 2

Hi @possibilities

You will want to use the function EOMONTH() to get the end date:

https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-ver16#c-eomonth-with-and-without-the-month_to_add-parameter

Of course to get the first date of the month, just use the number for days as 1.

-------------------------------------------------

If this is helpful please accept answer.

Answer 3

Building on what @Dillon Silzer said, you'll need to adjust for weekends yourself. You can do that by looking at the weekday of the end of the month. If it is 1 (Sunday) or 7 (Saturday) then step back -2 or -1 days. For the first of the month then you'd add 1 or 2 days accordingly. Perhaps something like this.

   \-- Last month  
   DECLARE @target DATETIME = DATEADD(month, -1, GETDATE())    
     
   \-- Get start and end just to save from repeating in the expression  
   DECLARE @startOfMonth DATETIME = DATEFROMPARTS(YEAR(@target), MONTH(@target), 1)  
   DECLARE @endOfMonth DATETIME = EOMONTH(@target)  
     
   SELECT @target  
     , CASE DATEPART(dw, @startOfMonth)   
       WHEN 1 THEN DATEADD(day, 1, @startOfMonth)  
   	WHEN 7 THEN DATEADD(day, 2, @startOfMonth)  
   	ELSE @startOfMonth END AS StartOfMonth  
     , CASE DATEPART(dw, @endOfMonth)  
       WHEN 1 THEN DATEADD(day, -2, @endOfMonth)  
   	WHEN 7 THEN DATEADD(day, -1, @endOfMonth)  
   	ELSE @endOfMonth END AS EndOfMonth  

Of course you could probably convert this to a function and pass in the date you care about and have it return the start and end as a table value if you wanted.

Answer 4

You need a calendar table for this. The first business day this month sis the 1st in most places, but maybe not in Switzerland. As I recall, that is their National Day, so it may be a public holiday. You may not be located in Switzerland, but you get the idea.

This article by Ed Pollack gives you more details on how to build a calendar table.

Answer 5

Hi @possibilities
Please check this query:

SET DATEFIRST 1  
DECLARE @MyDate DATETIME  
DECLARE @Begin_Date DATE  
DECLARE @End_Date DATE  
SET @MyDate = '24 Aug 2022'  
  
SET @End_Date = @MyDate - DATEPART(d,@MyDate)  
IF DATEPART(dw, @End_Date) > 5  
SET @End_Date = DATEADD(dd, -(DATEPART(dw, @End_Date)-5), @End_Date)  

SET @Begin_Date =DATEADD(mm,-1, @MyDate) - DATEPART(d,@MyDate) + 1  
IF DATEPART(dw, @Begin_Date) > 5  
SET @Begin_Date = DATEADD(dd, 8 - DATEPART(dw, @Begin_Date), @Begin_Date)  
  
SELECT @Begin_Date,@End_Date    

Note: This does not take any holidays into account. If you want to do so you will have to build a table of holidays. The above only returns the first/last Weekday of the previous month.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.