SQLServerCentral.com / SQL Server 2012 / SQL Server 2012 - T-SQL / Tsql to calculate workdays - holidays: for current month / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 16:23:26 GMT20RE: Tsql to calculate workdays - holidays: for current monthhttp://www.sqlservercentral.com/Forums/Topic1465324-3077-1.aspx[quote][b]David Smerchek (6/19/2013)[/b][hr]SELECT DATEDIFF (day, Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110), Convert(varchar(11),GETDATE(),110))- (2 * DATEDIFF(week, Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110), Convert(varchar(11),GETDATE(),110))) - CASE WHEN DATEPART(weekday, DATEADD(d,-(DAY(getdate())-1),getdate()) + @@DATEFIRST) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, GETDATE() + @@DATEFIRST) = 1 THEN 1 ELSE 0 END -count(Case when '01/01/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110) then 1 when '7/4/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1 when '9/2/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1 when '11/28/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1 when '12/25/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110) then 1 end) as WorkDays[/quote]The problem with such code is that it uses hardcoded dates that must be updated every year. If you forget, you can really mess up a whole bunch of things. Using a Calendar table would likely be a much better solution.Whatever solution you use, at least add some error handling for when you run out of dates or have exceeded hardcoded expectations.Sat, 03 Aug 2013 18:52:57 GMTJeff ModenRE: Tsql to calculate workdays - holidays: for current monthhttp://www.sqlservercentral.com/Forums/Topic1465324-3077-1.aspxIs there a question here or are you sharing something?Wed, 19 Jun 2013 12:39:09 GMTSean LangeTsql to calculate workdays - holidays: for current monthhttp://www.sqlservercentral.com/Forums/Topic1465324-3077-1.aspxSELECT DATEDIFF (day, Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110), Convert(varchar(11),GETDATE(),110))- (2 * DATEDIFF(week, Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110), Convert(varchar(11),GETDATE(),110))) - CASE WHEN DATEPART(weekday, DATEADD(d,-(DAY(getdate())-1),getdate()) + @@DATEFIRST) = 1 THEN 1 ELSE 0 END - CASE WHEN DATEPART(weekday, GETDATE() + @@DATEFIRST) = 1 THEN 1 ELSE 0 END -count(Case when '01/01/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110) then 1 when '7/4/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1 when '9/2/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1 when '11/28/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110)then 1 when '12/25/2013' between Convert(Varchar(11),DATEADD(d,-(DAY(getdate())-1),getdate()),110) and Convert(varchar(11),GETDATE(),110) then 1 end) as WorkDaysWed, 19 Jun 2013 12:23:30 GMTDavid Smerchek