sql 日期拆分

Question

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-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:000
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

Answer 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这个是上次发帖的，有人能帮我再改一下么？

Answer 2

select 
    dateadd(dd,num,starttime) starttime , endtime
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@starttime)<=endtime

Answer 3

create table tb1(id int,starttime datetime,endtime datetime)
insert into tb1 values(1 ,'2011-1-1 18:00:00', '2011-1-2 10:00:00')
insert into tb1 values(2 ,'2011-1-2 11:00:00', '2011-1-4 08:00:00')
go
select 
    id , convert(varchar(10),dateadd(dd,num,starttime),120) starttime , convert(varchar(10),endtime,120) endtime
from tb1 a,
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) b
where
    convert(varchar(10),dateadd(dd,num,starttime),120) <= convert(varchar(10),endtime,120)
order by id , starttimedrop table tb1/*
id          starttime  endtime    
----------- ---------- ---------- 
1           2011-01-01 2011-01-02
1           2011-01-02 2011-01-02
2           2011-01-02 2011-01-04
2           2011-01-03 2011-01-04
2           2011-01-04 2011-01-04（所影响的行数为 5 行）
*/

Answer 4

create table tb1(id int,starttime datetime,endtime datetime)
insert into tb1 values(1 ,'2011-1-1 18:00:00', '2011-1-2 10:00:00')
insert into tb1 values(2 ,'2011-1-2 11:00:00', '2011-1-4 08:00:00')
go
select 
    id , 
    case when datediff(dd,dateadd(dd,num,starttime),starttime) = 0 then convert(varchar(19),dateadd(dd,num,starttime),120)
         when datediff(dd,dateadd(dd,num,starttime),endtime) = 0 then convert(varchar(10),dateadd(dd,num,starttime),120) + ' 00:00:00'
         else convert(varchar(10),dateadd(dd,num,starttime),120) + ' 00:00:00'
    end starttime,
    case when datediff(dd,dateadd(dd,num,starttime),endtime) > 0 then convert(varchar(10),dateadd(dd,num,starttime) + 1,120) + ' 00:00:00'
         when datediff(dd,dateadd(dd,num,starttime),endtime) = 0 then convert(varchar(19),endtime,120)
    end endtime
from tb1 a,
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) b
where
    convert(varchar(10),dateadd(dd,num,starttime),120) <= convert(varchar(10),endtime,120)
order by id , starttimedrop table tb1/*
id          starttime           endtime             
----------- ------------------- ------------------- 
1           2011-01-01 18:00:00 2011-01-02 00:00:00
1           2011-01-02 00:00:00 2011-01-02 10:00:00
2           2011-01-02 11:00:00 2011-01-03 00:00:00
2           2011-01-03 00:00:00 2011-01-04 00:00:00 --貌似这里应该是这个.
2           2011-01-04 00:00:00 2011-01-04 08:00:00（所影响的行数为 5 行）
*/

Answer 5

create table tb(id INT,starttime datetime,endtime datetime)
insert into tb select 1,'2011-1-1 18:00:00','2011-1-2 10:00:00'
insert into tb select 2,'2011-1-2 11:00:00','2011-1-4 08:00:00'
go
;with cte as(
select id,a.starttime,convert(datetime,convert(varchar(10),dateadd(d,b.number+1,a.starttime),120))dt,a.endtime
 from tb a,master..spt_values b where b.type='p' and dateadd(d,b.number,a.starttime)<a.endtime
)
select id,starttime,dt as endtime from cte a where not exists(select 1 from cte where id=a.id and dt<a.dt)
union all
select id,dt,endtime from cte a where not exists(select 1 from cte where id=a.id and dt>a.dt)
union all
select a.id,a.dt,b.dt from cte a inner join cte b on a.id=b.id and datediff(d,a.dt,b.dt)=1 order by id,2
/*
id          starttime               endtime
----------- ----------------------- -----------------------
1           2011-01-01 18:00:00.000 2011-01-02 00:00:00.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-03 00:00:00.000
2           2011-01-03 00:00:00.000 2011-01-04 00:00:00.000
2           2011-01-04 00:00:00.000 2011-01-04 08:00:00.000(5 行受影响)*/
go
drop table tb

Answer 6

--按照你的要求再试试如下的.
create table tb1(id int,starttime datetime,endtime datetime)
insert into tb1 values(1 ,'2011-1-1 18:00:00', '2011-1-2 10:00:00')
insert into tb1 values(2 ,'2011-1-2 11:00:00', '2011-1-4 08:00:00')
go
select 
    id , 
    case when datediff(dd,dateadd(dd,num,starttime),starttime) = 0 then convert(varchar(19),dateadd(dd,num,starttime),120)
         when datediff(dd,dateadd(dd,num,starttime),endtime) = 0 then convert(varchar(10),dateadd(dd,num,starttime),120) + ' 00:00:00'
         else convert(varchar(10),dateadd(dd,num,starttime),120) + ' 00:00:00'
    end starttime,
    case when datediff(dd,dateadd(dd,num,starttime),endtime) <> 0 and datediff(dd,dateadd(dd,num,starttime),starttime) <> 0 then convert(varchar(10),dateadd(dd,num,starttime),120) + ' 23:59:59'
         when datediff(dd,dateadd(dd,num,starttime),endtime) > 0 then convert(varchar(10),dateadd(dd,num,starttime) + 1,120) + ' 00:00:00'
         when datediff(dd,dateadd(dd,num,starttime),endtime) = 0 then convert(varchar(19),endtime,120)
    end endtime
from tb1 a,
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) b
where
    convert(varchar(10),dateadd(dd,num,starttime),120) <= convert(varchar(10),endtime,120)
order by id , starttimedrop table tb1/*
id          starttime           endtime             
----------- ------------------- ------------------- 
1           2011-01-01 18:00:00 2011-01-02 00:00:00
1           2011-01-02 00:00:00 2011-01-02 10:00:00
2           2011-01-02 11:00:00 2011-01-03 00:00:00
2           2011-01-03 00:00:00 2011-01-03 23:59:59
2           2011-01-04 00:00:00 2011-01-04 08:00:00（所影响的行数为 5 行）
*/