SQL Server: recursive CTE with dates - types don't match

Question

Thought about creating a bit of a calendar with recursive CTE on dates. However I get the following error once running it:

Types don't match between the anchor and the recursive part in column "ddmmyyyy" of recursive query "cte_days"

Code:

WITH cte_days(n, weekd, ddmmyyyy) AS
(   
    SELECT 
        0, DATENAME(DW, '09-03-1983'), CONVERT(varchar, '09-03-1983', 10)
    UNION ALL   
    SELECT 
        0 + 1, DATENAME(weekday, DATEADD(day, 1, '09-03-1983')), DATEADD(day, 1, '09-03-1983')                      
    FROM 
        cte_days
    WHERE 
        n < 10
)   
SELECT * 
FROM cte_days

At that I tried testing data in ddmmyyyy column running two SELECTS in the middle and it was returned accurate:

0   Saturday    1983-09-03 00:00:00.000
1   Sunday      1983-09-04 00:00:00.000 

It also worked fine once creating a temp table with date column and sticking both values in there...

So can you help finding a mismatch?

Thank you.

Answer 1

SQL Server is picky about types of strings. But I would recommend focusing on dates in the CTE and doing your conversions afterwards:

WITH cte_days(n, dte) AS (
      SELECT 0, CONVERT(date, '19830309')
      UNION ALL
      SELECT n+1, DATEADD(day, 1, dte)
      FROM cte_days
      WHERE n < 10
     )
SELECT n, DATENAME(weekday, dte), CONVERT(VARCHAR(255), dte, 10)
FROM cte_days;

Answer 2

"Thought about creating a bit of a calendar with recursive CTE on dates." Honestly, I recommend a Tally over an rCTE. An rCTE is actually significantly slower than a Tally. This should be more than enough to get you started:

DECLARE @StartDate date = '19830309';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3) --Add more Cartisian Joins and increase value of TOP to get more rows
SELECT I,
       DATENAME(WEEKDAY,DATEADD(DAY, I, @StartDate)) AS DayName,
       DATEADD(DAY, I, @StartDate) AS DyDate
FROM Tally;

Answer 3

Using CAST function instead of CONVERT also helps:

WITH cte_days(n, weekd, ddmmyyyy) 
AS (    
 SELECT 0, DATENAME(DW, '09-03-1983'),CAST(CAST('09-03-1983' AS DATE) AS DATETIME)       
        UNION ALL   
 SELECT n+1, DATENAME(weekday, DATEADD(day, 1, '09-03-1983')), DATEADD(day, 1, '09-03-1983')                        
 FROM cte_days                                                                  
    WHERE n < 10                                                                        
)   
SELECT * FROM cte_days 

Answer 4

Moving your "FROM cte_days WHERE n < 10" outside of your CTE will work.

    --Your Code: 
WITH cte_days(n, weekd, ddmmyyyy) AS
(   
    SELECT 
        0, DATENAME(DW, '09-03-1983'), CONVERT(varchar, '09-03-1983', 10)
    UNION ALL   
    SELECT 
        0 + 1, DATENAME(weekday, DATEADD(day, 1, '09-03-1983')), DATEADD(day, 1, '09-03-1983')                      
    FROM 
        cte_days
    WHERE 
        n < 10
)   
SELECT * 
FROM cte_days

Working Code:

    WITH cte_days(n, weekd, ddmmyyyy) AS
(   
    SELECT 
        0, DATENAME(DW, '09-03-1983'), CONVERT(varchar, '09-03-1983', 10)
    UNION ALL   
    SELECT 
        0 + 1, DATENAME(weekday, DATEADD(day, 1, '09-03-1983')), DATEADD(day, 1, '09-03-1983')                      
)   
SELECT * 
FROM cte_days
    WHERE 
        n < 10

Above returns the following results:

n   weekd   ddmmyyyy
0   Saturday    1983-09-03 00:00:00.000
1   Sunday  1983-09-04 00:00:00.000