Get The First And Last Business Day Of The Previous Month

possibilities 41 Reputation points
2022-08-23T19:38:44.853+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,896 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-08-24T17:49:46.71+00:00

    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/

    1 person found this answer helpful.
    0 comments No comments

  2. Dillon Silzer 54,831 Reputation points
    2022-08-23T20:10:23.893+00:00

    Hi @possibilities

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

    234214-image.png

    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.


  3. Michael Taylor 49,246 Reputation points
    2022-08-23T20:32:03.34+00:00

    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.


  4. Erland Sommarskog 102.3K Reputation points
    2022-08-23T21:24:19.557+00:00

    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.

    0 comments No comments

  5. CosmogHong-MSFT 23,796 Reputation points Microsoft Vendor
    2022-08-24T06:48:44.007+00:00

    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.

    0 comments No comments