[Solved] Simplest way to fill working day table

by


You can use a recursive CTE to accomplish this. This only excludes the weekends. Using DATEFIRST you can figure out what day is a weekend. This query should work no matter what day of the week is set to DATEFIRST.

;WITH DatesCTE
 AS (

   SELECT CAST('2016-01-01' AS DATE) AS [workingDays]
   UNION ALL

   SELECT DATEADD(DAY, 1, workingdays)
   FROM DatesCTE
   WHERE DATEADD(DAY, 1, workingdays) < '2017-01-01'

)

SELECT *
FROM DatesCTE
WHERE ((DATEPART(dw, workingDays) + @@DATEFIRST) % 7) NOT IN (0, 1)
OPTION (MAXRECURSION 366)

solved Simplest way to fill working day table