SQL Statement below show you how to using SQL to get total working day in a month.
SQL Total working day in this SQL Statement is exclude Saturday and Sunday as working day.
Below is SQL Statement that get Total Working Day in a month.
DECLARE @my int
DECLARE @myDeduct int
DECLARE @day INT
DECLARE @mydate DATETIME
SET @mydate = getdate()
SET @myDeduct = 0
SET DateFirst 1 — Set it monday=1 (value)
–Saturday and Sunday on the first and last day of a month will Deduct 1
IF (DATEPART(weekday,(DATEADD(dd,-(DAY(@mydate)-1),@mydate))) > 5)
SET @myDeduct = @myDeduct + 1
IF (DATEPART(weekday,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))) > 5)
SET @myDeduct = @myDeduct + 1
SET @my = day(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))
select (((@my/7) * 5 + (@my%7)) – @myDeduct) as Working_Day_per_month
Return
Value = 21 day (for June 2007)
Value = 22 day (for July 2007)