| SQLTutorials | |
| Powered By PhICK1.com | |
SQL Get Working Day in a month Exclude Saturday and SundaySQL 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) powered by www.pHick1.com |
|