Get First Business day of Month, in MS SQL Server

Get First Business day of the Month

In this article, we will discuss how to get First day of the month and First Business day of the month (Excludes Saturday and Sunday if it comes on 1^st day of month), SQL server does not have direct function to get all this we need to write some workaround code.

First, get First day of the Month

--This part gets First date of the month.

Declare @YourDate Datetime='2019-1-15', @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(M, DATEDIFF(M, 0, @YourDate), 0)

Select(@FirstDateofMonth) 'First Date'

Now, get First Business day of the Month

--This part gets First date of the month.

Declare @YourDate Datetime='2019-1-5', @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(M, DATEDIFF(M, 0, @YourDate), 0)

Select(@FirstDateofMonth) 'First Date'

Select @FirstDateofMonth=

case 

       When DATEPART(DW,@FirstDateofMonth) = 1 Then DATEADd(day,1,@FirstDateofMonth) -- Gets Sunday

       When DATEPART(DW,@FirstDateofMonth) = 7 then DATEADd(day,2,@FirstDateofMonth) -- Gets Saturday

       else @FirstDateofMonth end --'Business Date of month'

       Return @FirstDateofMonth

Let’s write Function to get First Business day of the Month

Create  Function [dbo].[ufnGetFirstBusinessDay](@YourDate Datetime)

Returns Datetime as

Begin

--This part gets First date of the month.

Declare @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(m, DATEDIFF(m, 0, @YourDate), 0)

--This part gets First business day of the month,

--excludes Saturday and Sunday if it comes on 1st day of month

Select @FirstDateofMonth=

case 

       When DATEPART(DW,@FirstDateofMonth) = 1 Then DATEADd(day,1,@FirstDateofMonth)

       When DATEPART(DW,@FirstDateofMonth) = 7 then DATEADd(day,2,@FirstDateofMonth)

       else @FirstDateofMonth end --'Business Date of month'

       Return @FirstDateofMonth

End

--Select dbo.GetFirstBusinessDay('2019-6-15')