sql - Simplest way to fill working day table

Question

I have a table working_days with one column date of type date

I need to fill it with working days in USA.

Can you suggest how can I so this?

Manually it is too long.

Answer 1

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)