Add a record for every Week-Day of the year to a blank table

Question

I have a blank table that has two columns [ID] and [MyDate]. I would like to populate that table with all of the days of the current year MINUS weekends.

Is there a way to do this with a SQL query?

In this case I am using MSSQL T-SQL

I do not have any example code, as I am at a loss on where to get started for this scenario.

Do you have a numbers table? If so, it's quite easy. Also, what version of sql server are you working with? — Zohar Peled, Mar 15 '17 at 07:12

score 1 · Accepted Answer · edited May 23 '17 at 10:29

Using a numbers (Tally) table helps you to avoid using loops. If you don't already have a numbers table, you can use this script to create it:

SELECT TOP 10000 IDENTITY(int,0,1) AS Number
    INTO Tally
    FROM sys.objects s1       
    CROSS JOIN sys.objects s2 
ALTER TABLE Tally ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)

For more information about the creation of a numbers table, read this SO post.

Now that you have a numbers table, you can use a cte to generate the dates you want. I've used DATEFROMPARTS and GETDATE() to get Jauary 1st of the current year, if you are using a version of sql server below 2012 you need to use other methods for that:

DECLARE @StartDate Date,
        @EndDate Date 

SELECT @StartDate = DATEFROMPARTS(YEAR(GetDate()), 1, 1)
SELECT @EndDate = DATEADD(Year, 1, @StartDate)

Now, create a CTE to get the dates required using the numbers table, and insert the records from the cte to the table:

;WITH CTE AS                                       
(
    SELECT DATEADD(Day, Number, @StartDate) As TheDate
    FROM Tally
    WHERE DATEADD(Day, Number, @StartDate) < @EndDate
)

INSERT INTO WeekDays
SELECT TheDate
FROM CTE
WHERE DATEPART(WeekDay, TheDate) BETWEEN 2 AND 6

See a live demo on rextester.

beejm · Answer 2 · 2017-03-15T07:33:34.357

Here's my quick attempt at your problem. Just use your table instead

select 
CAST('2017-03-15' as datetime) as datestuff
into #test


Delete from #test

DECLARE @Y datetime = CAST('2017-12-31' AS DATE)

while @y != '2017-01-01'
    begin
        if DATENAME(DW, @y) not IN ('SUNDAY', 'SATURDAY')
            BEGIN
                INSERT INTO #test 
                SELECT @y 

            END


        SET @Y = DATEADD(DD, -1, @Y)
    end

    select * from #test

score 0 · Answer 3 · answered Mar 15 '17 at 07:22

This will do it. Here the 1 and the 7 represents Sunday and Saturday

CREATE TABLE T (
    ID INT NOT NULL IDENTITY(1,1),
    MyDate DATE NOT NULL)

DECLARE @Start DATE
DECLARE @End DATE

SET @Start = '20170101'
SET @End = '20171231'

WHILE @Start <= @End
BEGIN
    IF (DATEPART(DW, @Start) NOT IN (1,7))
    BEGIN
        INSERT INTO T (MyDate) VALUES (@Start)
    END 

    SET @Start = DATEADD(DAY, 1, @Start)
END

SELECT * FROM T

Add a record for every Week-Day of the year to a blank table

3 Answers3