SQL Server 2005+:
WITH dates AS (
SELECT t.startdate 'date'
FROM TABLE t
WHERE t.startdate = '1/1/2010'
UNION ALL
SELECT DATEADD(dd, 1, t.date)
FROM dates t
WHERE DATEADD(dd, 1, t.date) <= (SELECT t.enddate FROM TABLE t WHERE t.enddate = '1/5/2010'))
SELECT ...
FROM TABLE t
JOIN dates d ON d.date = t.date
如果你的日期相隔不超过2047天:
SELECT DATEADD(day,
n.number,
(SELECT t.startdate FROM TABLE t WHERE t.startdate = '1/1/2010')
)
FROM (SELECT DISTINCT number
FROM MASTER.dbo.SPT_VALUES
WHERE name IS NULL) n
WHERE DATEADD(day, n.number, (SELECT t.startdate FROM TABLE t WHERE t.startdate = '1/1/2010')) <= (SELECT t.endate FROM TABLE t WHERE t.endate = '1/5/2010')
with DateList as
(
select cast('1/1/2010' as datetime) DateValue
union all
select DateValue + 1
from DateList
where DateValue + 1 >= '1/1/2010' AND DateValue +1 <= '1/5/2010'
)
select CONVERT(varchar, DateValue, 101)
from DateList
OPTION (MAXRECURSION 0)
spt_values
方法,所以我会给你打分。 - AaronaughtOPTION (MAXRECURSION 10)
(或类似的选项)将导致此操作失败,这至少说明它实际上是通过递归执行的。 - Aaronaught