IF OBJECT_ID('tempdb..#Dates','U') IS NOT NULL DROP TABLE #Dates CREATE TABLE #Dates ( Dates CHAR(10) ) DECLARE @StartDate DATETIME=DATEADD(YEAR,-1,GETDATE()) DECLARE @EndDate DATETIME=GETDATE()SELECT @StartDate=MIN(FBeginDate) ,@EndDate=MAX(FEndDate) FROM 表A --yours
WHILE @StartDate<@EndDate BEGIN INSERT INTO #Dates SELECT CONVERT(CHAR(10),@StartDate,120) SELECT @StartDate=DATEADD(DAY,1,@StartDate) ENDSELECT * FROM #Dates
你看看,是这样吗 --drop table tbcreate table tb(FBeginDate datetime,FEndDate datetime)insert into tb select '2010-10-01','2010-10-01' union all select '2010-10-01','2010-10-07' union all select '2011-01-30','2011-02-12' ;with t as ( select 1 as number union all select number + 1 from t where t.number < 100 )select tb.FBeginDate, tb.FEndDate, dateadd(day,t.number-1,FBeginDate) as '两个日期之间的天' from tb inner join t on datediff(day,FBeginDate,FEndDate) +1 >= t.number order by tb.FBeginDate, '两个日期之间的天'
谢谢了 这是我的代码 create table #gala (fdate datetime) ;with tmep as ( select fdate=FBeginDate,op=FBeginDate,ed=FEndDate from Bd_Holiday union all select fdate=fdate+1,op=op,ed=ed from tmep where fdate<ed ) insert into #gala select distinct fdate from tmep order by fdate desc
谢谢了 这是我的代码 create table #gala (fdate datetime) ;with tmep as ( select fdate=FBeginDate,op=FBeginDate,ed=FEndDate from Bd_Holiday union all select fdate=fdate+1,op=op,ed=ed from tmep where fdate<ed ) insert into #gala select distinct fdate from tmep order by fdate desc 哦 对的,就是这样,把递归查询的结果,insert 到你的表中就可以了。
CREATE TABLE #Dates
(
Dates CHAR(10)
)
DECLARE @StartDate DATETIME=DATEADD(YEAR,-1,GETDATE())
DECLARE @EndDate DATETIME=GETDATE()SELECT @StartDate=MIN(FBeginDate)
,@EndDate=MAX(FEndDate)
FROM 表A --yours
WHILE @StartDate<@EndDate
BEGIN
INSERT INTO #Dates
SELECT CONVERT(CHAR(10),@StartDate,120)
SELECT @StartDate=DATEADD(DAY,1,@StartDate)
ENDSELECT *
FROM #Dates
--drop table tbcreate table tb(FBeginDate datetime,FEndDate datetime)insert into tb
select '2010-10-01','2010-10-01'
union all select '2010-10-01','2010-10-07'
union all select '2011-01-30','2011-02-12'
;with t
as
(
select 1 as number
union all
select number + 1
from t
where t.number < 100
)select tb.FBeginDate,
tb.FEndDate,
dateadd(day,t.number-1,FBeginDate) as '两个日期之间的天'
from tb
inner join t
on datediff(day,FBeginDate,FEndDate) +1 >= t.number
order by tb.FBeginDate,
'两个日期之间的天'
/*
FBeginDate FEndDate 两个日期之间的天
2010-10-01 00:00:00.000 2010-10-01 00:00:00.000 2010-10-01 00:00:00.000
2010-10-01 00:00:00.000 2010-10-07 00:00:00.000 2010-10-01 00:00:00.000
2010-10-01 00:00:00.000 2010-10-07 00:00:00.000 2010-10-02 00:00:00.000
2010-10-01 00:00:00.000 2010-10-07 00:00:00.000 2010-10-03 00:00:00.000
2010-10-01 00:00:00.000 2010-10-07 00:00:00.000 2010-10-04 00:00:00.000
2010-10-01 00:00:00.000 2010-10-07 00:00:00.000 2010-10-05 00:00:00.000
2010-10-01 00:00:00.000 2010-10-07 00:00:00.000 2010-10-06 00:00:00.000
2010-10-01 00:00:00.000 2010-10-07 00:00:00.000 2010-10-07 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-01-30 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-01-31 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-01 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-02 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-03 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-04 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-05 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-06 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-07 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-08 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-09 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-10 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-11 00:00:00.000
2011-01-30 00:00:00.000 2011-02-12 00:00:00.000 2011-02-12 00:00:00.000
*/
这是我的代码
create table #gala (fdate datetime)
;with tmep as
(
select fdate=FBeginDate,op=FBeginDate,ed=FEndDate from Bd_Holiday
union all
select fdate=fdate+1,op=op,ed=ed from tmep where fdate<ed
)
insert into #gala select distinct fdate from tmep order by fdate desc
这是我的代码
create table #gala (fdate datetime)
;with tmep as
(
select fdate=FBeginDate,op=FBeginDate,ed=FEndDate from Bd_Holiday
union all
select fdate=fdate+1,op=op,ed=ed from tmep where fdate<ed
)
insert into #gala select distinct fdate from tmep order by fdate desc 哦 对的,就是这样,把递归查询的结果,insert 到你的表中就可以了。