34,576
社区成员
发帖
与我相关
我的任务
分享
if not object_id('ta') is null
drop table ta
Go
Create table ta([Store] nvarchar(2),[StartDate] Datetime,[EndDate] Datetime)
Insert ta
select 'A店', '20111230', '20120101'
Go
select Store,dateadd(day,s.number,[StartDate]) from ta t,master..spt_values s where s.type='p' and s.number between 0 and DATEDIFF(DAY,t.StartDate,t.EndDate)
Store
----- -----------------------
A店 2011-12-30 00:00:00.000
A店 2011-12-31 00:00:00.000
A店 2012-01-01 00:00:00.000
(3 行受影响)
学习了
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
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
*/