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
给你一段试试,实际操作的时候,修改@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 结果
;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
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
.......
*/
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
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
结果
(
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