You don't want to use datepart(day...) here. That will return the day of the month. You want to use datepart(weekday...)
I like to use the full word instead of the abbreviation because it is much easier to read. So I use day instead dd etc.
If you have a tally table (you do right?), you can use it here.
SELECT min(NewDate)
FROM #Holidays
cross apply
(
select dateadd(day, N, HolidayDate) as NewDate
from Tally where N < 8
)x
where HolidayDate > getdate()
and DATEPART(weekday, NewDate) NOT IN (7,1)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/