CODEWITHSUNDEEP
sqlsql-serverdate
screechOwl
screechOwl

Reputation: 28189

SQL - Create table with one column that has date by week

I'd like to create a table that has one column and the column is weekly dates starting with 1/1/2015 thru the end of 2015. Is there an easy way to do this?

ie

CREATE TABLE Week_End_Dates
(
WeekEndDt date
);

INSERT INTO Week_End_Dates(WeekEndDt)
VALUES ('2015-01-01'),('2015-01-08')

Upvotes: 1

Views: 2045

Answers (1)

jpw
jpw

Reputation: 44921

There are plenty of ways to generate a date sequence. One is to use a common table expression like this:

WITH CTE (DT) AS 
(
    SELECT CAST('2015-01-01' AS DATE) DT
    UNION ALL
    SELECT DATEADD(WEEK, 1, DT)
    FROM CTE
    WHERE DATEADD(WEEK, 1, DT) < '2016-01-01'
)
INSERT INTO Week_End_Dates(WeekEndDt)
SELECT * FROM CTE

It's not the most efficient (and can easily be optimized in several ways), but it should do what you seem to want.

The query would generate a result like:

WeekEndDt
2015-01-01
2015-01-08
2015-01-15
2015-01-22
2015-01-29
... etc until 2015-12-31

Upvotes: 1

Related Questions