返回连续日期的临时表 [英] Return temp table of continuous dates
问题描述
我需要创建一个返回连续日期表的函数.我会在一分钟内通过最大日期.
I need to create a function that returns a table of continuous dates. I would pass in a min & max date.
我希望它能够像这样被调用:
I expect it to be able to be called like this:
SELECT * FROM GetDates('01/01/2009', '12/31/2009')
我目前有一个执行此操作的存储过程,但要求发生了变化,现在我需要在联合中包含返回的数据:
I currently have a stored proc that does this, but requirements changed and now I need to do include the returned data from within a union:
with mycte as
(
select cast(@minDate as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 <= @maxDate
)
select DateValue
from mycte
option (maxrecursion 1000)
然而,问题是我需要将递归设置为大于 100.根据 Gail Erickson [MS] 在 eggheadcafe,目前不支持.
The problem, however, is that I need to set the recursion to be greater than 100. According to a post by Gail Erickson [MS] on eggheadcafe, this is not currently supported.
如果不创建一个真正的(非临时) 表,其中只有日期,有没有办法做到这一点?
Without creating a real (not temporary) table with just date in it, is there a way to do this?
我使用的是 SqlServer2005.
I am using SqlServer2005.
推荐答案
您最好的选择是实际拥有一个实际的日期表.即使在很长一段时间内也没有那么多,而且比从临时表或递归 ctes 中实时实现它们要快得多.
Your best option is to actually have a physical table of dates. There aren't that many for even long periods, and will be much faster than materializing them on-the-fly from temp tables or recursive ctes.
这篇关于返回连续日期的临时表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!