如何按日期拆分时间段

tb1：
id starttime endtime
1 2011-1-1 18:00:00 2011-1-2 10:00:00
2 2011-1-2 11:00:00 2011-1-4 08:00:00

求如何分拆出：

id starttime endtime
1 2011-1-1 18:00:00 2011-1-1 23:59:59
1 2011-1-2 00:00:00 2011-1-2 10:00:00
2 2011-1-2 11:00:00 2011-1-2 23:59:59
2 2011-1-3 00:00:00 2011-1-3 23:59:59
2 2011-1-4 00:00:00 2011-1-4 08:00:00

5 个解决方案

#1

declare @tb1 table(id int,starttime datetime,endtime datetime)
insert @tb1 select
1, '2011-1-1 18:00:00', '2011-1-2 10:00:00'
union all select
2, '2011-1-2 11:00:00', '2011-1-4 08:00:00'

;with cte as(
select * from @tb1 where datediff(day,starttime,endtime) = 0
union all
select id,starttime,endtime = convert(varchar(10),starttime,120) + ' 23:59:59'
from @tb1 where datediff(day,starttime,endtime) > 0
union all
select id,starttime = convert(varchar(10),endtime,120) ,endtime
from @tb1 where datediff(day,starttime,endtime) > 0
union all
select id,starttime = convert(varchar(10),dateadd(day,1,starttime),120) 
,endtime = convert(varchar(10),dateadd(day,-1,endtime),120)  + ' 23:59:59'
from @tb1 where datediff(day,starttime,endtime) > 1
union all
select id,starttime,endtime = convert(varchar(10),starttime,120) + ' 23:59:59'
from cte where datediff(day,starttime,endtime) > 0
union all
select id,starttime = convert(varchar(10),endtime,120) ,endtime
from cte where datediff(day,starttime,endtime) > 0
union all
select id,starttime = convert(varchar(10),dateadd(day,1,starttime),120) 
,endtime = convert(varchar(10),dateadd(day,-1,endtime),120)  + ' 23:59:59'
from cte where datediff(day,starttime,endtime) > 1
)
select * from cte
order by id,starttime

--结果
id	starttime	endtime
1	2011-01-01 18:00:00.000	2011-01-01 23:59:59.000
1	2011-01-02 00:00:00.000	2011-01-02 10:00:00.000
2	2011-01-02 11:00:00.000	2011-01-02 23:59:59.000
2	2011-01-03 00:00:00.000	2011-01-03 23:59:59.000
2	2011-01-04 00:00:00.000	2011-01-04 08:00:00.000

#2

更正

declare @tb1 table(id int,starttime datetime,endtime datetime)
insert @tb1 select
1, '2011-1-1 18:00:00', '2011-1-2 10:00:00'
union all select
2, '2011-1-2 11:00:00', '2011-1-14 08:00:00'

;with cte as(
select * from @tb1 where datediff(day,starttime,endtime) = 0
union all
select id,starttime,endtime = convert(varchar(10),starttime,120) + ' 23:59:59'
from @tb1 where datediff(day,starttime,endtime) > 0
union all
select id,starttime = convert(varchar(10),endtime,120) ,endtime
from @tb1 where datediff(day,starttime,endtime) > 0
union all
select id,starttime = convert(varchar(10),dateadd(day,1,starttime),120) 
,endtime = convert(varchar(10),dateadd(day,-1,endtime),120)  + ' 23:59:59'
from @tb1 where datediff(day,starttime,endtime) > 1
union all
select id,starttime,endtime = convert(varchar(10),starttime,120) + ' 23:59:59'
from cte where datediff(day,starttime,endtime) > 0
union all
select id,starttime = convert(varchar(10),endtime,120) ,endtime
from cte where datediff(day,starttime,endtime) > 0
union all
select id,starttime = convert(varchar(10),dateadd(day,1,starttime),120) 
,endtime = convert(varchar(10),dateadd(day,-1,endtime),120)  + ' 23:59:59'
from cte where datediff(day,starttime,endtime) > 1
)
select * from cte
where datediff(day,starttime,endtime) = 0
order by id,starttime

--结果
id	starttime	endtime
id	starttime	endtime
1	2011-01-01 18:00:00.000	2011-01-01 23:59:59.000
1	2011-01-02 00:00:00.000	2011-01-02 10:00:00.000
2	2011-01-02 11:00:00.000	2011-01-02 23:59:59.000
2	2011-01-03 00:00:00.000	2011-01-03 23:59:59.000
2	2011-01-04 00:00:00.000	2011-01-04 23:59:59.000
2	2011-01-05 00:00:00.000	2011-01-05 23:59:59.000
2	2011-01-06 00:00:00.000	2011-01-06 23:59:59.000
2	2011-01-07 00:00:00.000	2011-01-07 23:59:59.000
2	2011-01-08 00:00:00.000	2011-01-08 23:59:59.000
2	2011-01-09 00:00:00.000	2011-01-09 23:59:59.000
2	2011-01-10 00:00:00.000	2011-01-10 23:59:59.000
2	2011-01-11 00:00:00.000	2011-01-11 23:59:59.000
2	2011-01-12 00:00:00.000	2011-01-12 23:59:59.000
2	2011-01-13 00:00:00.000	2011-01-13 23:59:59.000
2	2011-01-14 00:00:00.000	2011-01-14 08:00:00.000

#3

是我多了两行,还是楼主少了两行?

create table tb1(id int,starttime datetime,endtime datetime)
insert into tb1 select 1,'2011-1-1 18:00:00','2011-1-2 10:00:00'
insert into tb1 select 2,'2011-1-2 11:00:00','2011-1-4 08:00:00'
go
select id,starttime as dt from tb1
union all
select id,endtime from tb1
union all
select id,convert(varchar(10),endtime,120) from tb1
union all
select id,dateadd(s,-1,convert(varchar(10),endtime,120)) from tb1 order by id,dt
/*
id          dt
----------- -----------------------
1           2011-01-01 18:00:00.000
1           2011-01-01 23:59:59.000
1           2011-01-02 00:00:00.000
1           2011-01-02 10:00:00.000
2           2011-01-02 11:00:00.000
2           2011-01-03 23:59:59.000
2           2011-01-04 00:00:00.000
2           2011-01-04 08:00:00.000

(8 行受影响)

*/
go
drop table tb1

#4

高啊。。谢谢！

#5

搞错了，结果应该是：
1 2011-1-1 18:00:00 2011-1-2 00:00:00 
1 2011-1-2 00:00:00 2011-1-2 10:00:00
2 2011-1-2 11:00:00 2011-1-3 00:00:00 
2 2011-1-3 00:00:00 2011-1-4 00:00:00 
2 2011-1-4 00:00:00 2011-1-4 08:00:00

能帮我改一下么？