CREATE TABLE NonBusinessDays(NBDDate smalldatetime)GOSELECT datediff(day, @creation_date, getdate()) - count(*) AS OutstandingFROM NonBusinessDaysWHERE NBDDate >= @creation_date and NBDDate < getdate()
quote:Here's a user defined function that does what you need. You will have to create tblHolidays and populate it with all the holidays you want excluded.
why can't u use the post by NPeeters. Thats how u should be doing."Holidays" vary by divsion by company by location. So having a holiday table would be the best way to handle any situation.
As suggested, do a search on this forum... there's a particullarly good and nasty fast function to calculate Work Days (week days, actually) at http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp
Also as suggested, you will need to incorporate a "Holiday Table"... you can find out how to do that (and more with calendar tables and the like) at
Adam Mechanic was the guy who turned me on to Tally or Numbers tables. Haven't needed a full blown calendar table but have made a holidy table. It's definitely worth the read... please ignore the fact that he used an (ungh! ) cursor for one thing... he must'a been feeling poorly that day
Yes, that function is OK, but only for English speaking servers.
Change SQL Server collation - and it will fail.