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 1st 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')
