循环插入一年内的时间段

[sx19821116]

效果如下图所显示的 跪求

[水族杰纶]

--类似 while @date<='2011-12-31' begin insert... insert... set @date=dateadd(day,1,@date) end

[LongRui888]

declare @start datetime
declare @end datetime
declare @t table(id int identity(1,1),startpoint datetime,endpoint datetime)

set @start = '2011-01-01'
set @end = '2011-12-31'

while @start <= @end
begin
   insert into @t(startpoint,endpoint)
   select DATEADD(hour,9,@start),DATEADD(hour,12,@start) union all
   select DATEADD(hour,14,@start),DATEADD(hour,18,@start)
   
   set @start = dateadd(day,1,@start)
end


select * from @t
/*
id	startpoint	endpoint
1	2011-01-01 09:00:00.000	2011-01-01 12:00:00.000
2	2011-01-01 14:00:00.000	2011-01-01 18:00:00.000
3	2011-01-02 09:00:00.000	2011-01-02 12:00:00.000
4	2011-01-02 14:00:00.000	2011-01-02 18:00:00.000
5	2011-01-03 09:00:00.000	2011-01-03 12:00:00.000
6	2011-01-03 14:00:00.000	2011-01-03 18:00:00.000
7	2011-01-04 09:00:00.000	2011-01-04 12:00:00.000
8	2011-01-04 14:00:00.000	2011-01-04 18:00:00.000
9	2011-01-05 09:00:00.000	2011-01-05 12:00:00.000
10	2011-01-05 14:00:00.000	2011-01-05 18:00:00.000
.......
*/

[lzw_0736]

declare @start datetime
declare @end datetime
declare @t table(id int identity(1,1),startpoint datetime,endpoint datetime)
 
set @start = '2011-01-01'
set @end = '2011-12-31'

;WITH a1 AS
(
select DATEADD(hour,9,@start) date_start,DATEADD(hour,12,@start) date_end union all
SELECT DATEADD(day,1,date_start),DATEADD(day,1,date_end)
FROM a1
WHERE date_start<@end
)
insert into @t(startpoint,endpoint)
SELECT * FROM a1 UNION all
SELECT DATEADD(hour,5,date_start) date_start,DATEADD(hour,6,date_end) FROM a1
ORDER BY 1
OPTION(MAXRECURSION 0)
 
select * from @t

[山寨DBA]

给你一段试试，实际操作的时候，修改@date时间

create table #last(
	id int IDENTITY(1,1) ,
	startpoint datetime null,
	endpoint datetime null,
	workday int null,
	descp varchar(50) null
)

declare @date datetime
set @date='2013-01-01'
while @date<=convert(datetime,'2013-02-01',121)
 begin
	insert into #last
	select @date+' 09:00:00.000',@date+' 12:00:00.000',datepart(week,@date),''
	insert into #last
	select @date+' 14:00:00.000',@date+' 18:00:00.000',datepart(week,@date),''
	set @date=dateadd(day,1,@date)
 end 

select * From #last

结果

[山寨DBA]

结果贴错了图片，正确图片

[山寨DBA]

哎呀，补贴了，找不到图了，我晕。。。。 上面的代码，你自己执行下，结果就出来了

[Leon_He2014]

;with t1 as
(
select 9 h1,12 h2
union all select 14,18
),t2 as
(
select DATEADD(day,number,'2011-01-01') startdate
from master.dbo.spt_values
where type='p'
)
select ROW_NUMBER() OVER(order by dateadd(hour,h1,startdate) ) id,dateadd(hour,h1,startdate) StartPoint,dateadd(hour,h2,startdate) endpoint
,DATEPART(WEEKDAY,dateadd(d,-1,startdate)) weekdate
from t2
cross join t1