22,300
社区成员




DECLARE @beginTime DATETIME,@endTime DATETIME
SET @beginTime='2015-05-20'
SET @endTime='2015-06-20'
/*
2048 * 2048 = 4194304 天 = 11491.24383561644 年, 足够用了
*/
;WITH t1 AS (
SELECT sv.number AS n FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P'
)
,t2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid FROM t1 AS a CROSS APPLY t1 AS b
)
SELECT DATEADD(DAY,t2.rid,@beginTime) AS rq
FROM t2
WHERE t2.rid BETWEEN 0 AND DATEDIFF(DAY,@beginTime,@endTime)
/*
rq
2015-05-20 00:00:00.000
2015-05-21 00:00:00.000
2015-05-22 00:00:00.000
2015-05-23 00:00:00.000
2015-05-24 00:00:00.000
2015-05-25 00:00:00.000
2015-05-26 00:00:00.000
2015-05-27 00:00:00.000
2015-05-28 00:00:00.000
2015-05-29 00:00:00.000
2015-05-30 00:00:00.000
2015-05-31 00:00:00.000
2015-06-01 00:00:00.000
2015-06-02 00:00:00.000
2015-06-03 00:00:00.000
2015-06-04 00:00:00.000
2015-06-05 00:00:00.000
2015-06-06 00:00:00.000
2015-06-07 00:00:00.000
2015-06-08 00:00:00.000
2015-06-09 00:00:00.000
2015-06-10 00:00:00.000
2015-06-11 00:00:00.000
2015-06-12 00:00:00.000
2015-06-13 00:00:00.000
2015-06-14 00:00:00.000
2015-06-15 00:00:00.000
2015-06-16 00:00:00.000
2015-06-17 00:00:00.000
2015-06-18 00:00:00.000
2015-06-19 00:00:00.000
2015-06-20 00:00:00.000
*/
DECLARE @beginTime DATETIME,@endTime DATETIME
SET @beginTime='2015-05-20'
SET @endTime='2015-06-20'
SELECT DATEADD(DAY,sv.number,@beginTime) AS rq
FROM MASTER.dbo.spt_values AS sv
WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND DATEDIFF(DAY,@beginTime,@endTime)
/*
rq
2015-05-20 00:00:00.000
2015-05-21 00:00:00.000
2015-05-22 00:00:00.000
2015-05-23 00:00:00.000
2015-05-24 00:00:00.000
2015-05-25 00:00:00.000
2015-05-26 00:00:00.000
2015-05-27 00:00:00.000
2015-05-28 00:00:00.000
2015-05-29 00:00:00.000
2015-05-30 00:00:00.000
2015-05-31 00:00:00.000
2015-06-01 00:00:00.000
2015-06-02 00:00:00.000
2015-06-03 00:00:00.000
2015-06-04 00:00:00.000
2015-06-05 00:00:00.000
2015-06-06 00:00:00.000
2015-06-07 00:00:00.000
2015-06-08 00:00:00.000
2015-06-09 00:00:00.000
2015-06-10 00:00:00.000
2015-06-11 00:00:00.000
2015-06-12 00:00:00.000
2015-06-13 00:00:00.000
2015-06-14 00:00:00.000
2015-06-15 00:00:00.000
2015-06-16 00:00:00.000
2015-06-17 00:00:00.000
2015-06-18 00:00:00.000
2015-06-19 00:00:00.000
2015-06-20 00:00:00.000
*/
declare @StartDate DATETIME = '2015/05/01'
declare @EndDate DATETIME ='2015/06/03';
with cte as
(
select @StartDate dday
union all
select dday +1 from cte
where dday < @EndDate
)
select * from cte
option(maxrecursion,0)
declare @StartDate DATETIME = '2015/05/01'
declare @EndDate DATETIME ='2015/06/03'
select dateadd(day,number,@StartDate) as dt
from master.dbo.spt_values where type ='P'
and number <=DATEDIFF(day, @StartDate, @EndDate)
/*
dt
-----------------------
2015-05-01 00:00:00.000
2015-05-02 00:00:00.000
2015-05-03 00:00:00.000
2015-05-04 00:00:00.000
2015-05-05 00:00:00.000
2015-05-06 00:00:00.000
2015-05-07 00:00:00.000
2015-05-08 00:00:00.000
...
*/