向"日期"列添加值导致溢出

Question

with MyDates as 
(
SELECT
cast( '01-oct-2020' as date ) AS MyDate,
DATENAME( DW, cast('01-oct-2020' as date ) ) AS NameOfDay
UNION ALL
SELECT
DATEADD( DAY, 1, MyDate ) AS MyDate,
DATENAME( DW, DATEADD( DAY, 1, MyDate ) ) AS NameOfDay
FROM
MyDates
WHERE
DATEADD( DAY, 1, MyDate ) < DATEADD( DAY, -1, DATEADD( MONTH, 3, MyDate ) )
)
select
*
from
MyDates
order by
MyDate desc
option (maxrecursion 0)

我正在尝试上面的代码。如果我不运行order by子句，它会运行得很好。

使用order by子句时抛出错误

向'date'列添加值导致溢出

请帮

Answer 1

据我所知，你的问题与ORDER BY无关。问题是递归CTE。

这个条件总是求值为true:

DATEADD( DAY, 1, MyDate ) < DATEADD( DAY, -1, DATEADD( MONTH, 3, MyDate ) )

所以递归永远不会结束。为什么?MyDate总是小于未来3个月零1天的日期。这只引用单行中的列。所以，这不是你想要的。

我猜你想要这样的东西:

with MyDates as (
SELECT CAST( '2020-10-01' as date ) AS MyDate,
DATENAME(weekday, '2020-10-01') AS NameOfDay,
CAST( '2020-10-01' as date )  as orig_date
UNION ALL
SELECT DATEADD(DAY, 1, MyDate ) AS MyDate,
DATENAME(weekday, DATEADD( DAY, 1, MyDate ) ) AS NameOfDay,
orig_date
FROM MyDates
WHERE MyDate < DATEADD( MONTH, 3, orig_date )
)
select *
from  MyDates
order by  MyDate desc
option (maxrecursion 0);

这是一个db<>小提琴