日期:2014-05-18  浏览次数:20551 次

怎么插入两个日期之间的所有日期?
Table1
Store StartDate EndDate 
A店 20111230 20120101

Table2
Store Date
A店 20111230
A店 20111231
A店 20120101
由Table1 生成Table2 怎么写

------解决方案--------------------
SQL code
if not object_id('ta') is null
    drop table ta
Go
Create table ta([Store] nvarchar(2),[StartDate] Datetime,[EndDate] Datetime)
Insert ta
select N'A店','20111230','20120101'UNION ALL
select N'B店','20111130','20111205'
Go
SELECT a.[Store],
       DATEADD(DAY,b.number,a.[StartDate])[Date]
FROM master..spt_values b,ta a
WHERE b.type='P'
AND   b.number BETWEEN 0 AND DATEDIFF(day,a.[StartDate],a.[EndDate]) 
/*
Store Date
----- -----------------------
A店    2011-12-30 00:00:00.000
A店    2011-12-31 00:00:00.000
A店    2012-01-01 00:00:00.000
B店    2011-11-30 00:00:00.000
B店    2011-12-01 00:00:00.000
B店    2011-12-02 00:00:00.000
B店    2011-12-03 00:00:00.000
B店    2011-12-04 00:00:00.000
B店    2011-12-05 00:00:00.000
*/

------解决方案--------------------
SQL code

create table t1
(
    store varchar(10),
    startdate datetime,
    enddate datetime
)
insert into t1 
select 'A店', '20111230', '20120101'
select * from t1

select store,DATEADD(DAY,number,a.startdate) as riqi
from t1 as a with(nolock) cross join master..spt_values as b with(nolock)
where b.type='P' and DATEADD(DAY,number,a.startdate)<=a.enddate

-------------------------------
store    riqi
A店    2011-12-30 00:00:00.000
A店    2011-12-31 00:00:00.000
A店    2012-01-01 00:00:00.000