On MS SQL, how to create a table that only consists of dates and days of the week?

Question

I am need to create a table on MS SQL 2014 showing a date and a weekday column. It need to start at 2014-01-01 and the last day should be today. It should look like the one bellow:

   days_date      weekday
   2014-01-01     Wednesday
   2014-01-02     Thursday
   2014-01-03     Friday
   ...            ... 
   2018-03-06     Tuesday

My relevant script is here:

 CREATE TABLE [dbo].[new_table](
     [days_date] [date] NOT NULL,
     [weekday] [nvarchar](50) NULL
 ) ON [PRIMARY]

GO

 WITH CTE (DT) AS 
 (
    SELECT CAST('2014-01-01' AS DATE) DT
    UNION ALL
    SELECT DATEADD(DAY, 1, DT)
    FROM CTE
    WHERE DATEADD(DAY, 1, DT) < '2018-03-06'
 )
 INSERT INTO [dbo].[new_table]
          ([days_date]
          ,[weekday])
 VALUES
       (select * from CTE,
       ,select DATENAME(CTE,GETDATE()))
 GO

Getting some errors here:

 Msg 156, Level 15, State 1, Line 13
 Incorrect syntax near the keyword 'select'.
 Msg 156, Level 15, State 1, Line 14
 Incorrect syntax near the keyword 'SELECT'.
 Msg 155, Level 15, State 1, Line 14
 'CTE' is not a recognized datename option.

How should I fix the script?

Look at [this answer](https://stackoverflow.com/a/32474751/5089204). There you find a complete example introducing a numbers table including various helpful columns. — Shnugo, Mar 06 '18 at 11:27
But what if you need other columns later? Here [Aaron has a good post on how to create a date dimension table](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/) — S3S, Mar 06 '18 at 16:41

score 1 · Answer 1 · answered Mar 06 '18 at 11:24

you had some syntax errors in your insert query. More, the datename you was trying to insert was related to getdate() which (i guess) is not your target. You should refer to your CTE date alias: DT try this:

CREATE TABLE [dbo].[new_table](
     [days_dates] [date] NOT NULL,
     [weekday] [nvarchar](50) NULL
 ) ON [PRIMARY]

GO

 WITH CTE (DT) AS 
 (
    SELECT CAST('2014-01-01' AS DATE) DT
    UNION ALL
    SELECT DATEADD(DAY, 1, DT)
    FROM CTE
    WHERE DATEADD(DAY, 1, DT) < '2018-03-06'
 )
 INSERT INTO [dbo].[new_table]
          ([days_dates]
          ,[weekday])
       (select * , DATENAME(WEEKDAY,dt) from CTE)
       OPTION (MaxRecursion 0)
 GO

Jayasurya Satheesh · Answer 2 · 2018-03-06T11:39:09.857

1

You Can't use a SELECT statement inside the Values, But instead, you replace values using the select.

Change your insert Query as below

;WITH CTE 
(
    DT
) AS 
(
    SELECT CAST('2014-01-01' AS DATE) DT
    UNION ALL
    SELECT 
       DATEADD(DAY, 1, DT)
       FROM CTE
          WHERE DATEADD(DAY, 1, DT) < '2018-03-06'
)
INSERT INTO [dbo].[new_table]
(
    [days_date],
    [weekday]
)
select
    DT,
    DATENAME(DW,DT)
    from CTE
OPTION(maxrecursion 0)

edited Mar 06 '18 at 11:39

answered Mar 06 '18 at 11:26

Jayasurya Satheesh

7,826
3
22
39

you're missing the `MAXRECURSION` option, without it you cant add more then 100 values in that table. – B3S Mar 06 '18 at 11:36

score 1 · Answer 3 · answered Mar 06 '18 at 11:41

-- The following error message Will encountered:

Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. To overcome this error message, the MAXRECURSION query hint can be specified to increase the maximum number of recursion from the default value of 100 to a maximum of 1000.

 CREATE TABLE [dbo].[new_table](
         [days_date] [date] NOT NULL,
         [weekday] [nvarchar](50) NULL
     ) ON [PRIMARY]

; WITH CTE (DT) AS 
 (
    SELECT CAST('2014-01-01' AS DATE) DT
    UNION ALL
    SELECT DATEADD(DAY, 1, DT)
    FROM CTE 
    WHERE DATEADD(DAY, 1, DT) < =GETDATE()
 )

 INSERT INTO [dbo].[new_table]
          ([days_date]
          ,[weekday])
 SELECT DT,DATENAME(DW,DT)  
 FROM CTE OPTION (maxrecursion 0)

 GO
 SELECT * FROM new_table

-- To Over come Such Issue Use OPTION (maxrecursion 0)

there already is another identical answer.. please check before repost — B3S, Mar 06 '18 at 11:42
@B3s The user Should know why The OPTION (maxrecursion 0) Is getting use here — Alfaiz Ahmed, Mar 06 '18 at 11:44

On MS SQL, how to create a table that only consists of dates and days of the week?

3 Answers3