3,499
社区成员
发帖
与我相关
我的任务
分享如何用SQL剔除夜间时间
有一个表 有两列 开始时间 结束时间 需要知道结束时间减去开始时间看用了多长时间 但是要剔除晚上20:00到早上8:00的这段时间 开始时间和结束时间有可能不是同一天，也有可能是同一天 谁有办法用SQL实现这个东西 帮帮忙 谢谢了
...全文
请发表友善的回复…
发表回复
sd722522 2012-03-21
- 打赏
- 举报
ORACLE 出门左拐第一个胡同问问吧
开着拖拉机泡妞 2012-03-15
- 打赏
- 举报
go
if object_id('tbl')is not null
drop table tbl
go
create table tbl(
begindate datetime,
enddate datetime
)
go
insert tbl
select '2012-01-01 09:00:00','2012-01-01 19:00:00' union all
select '2012-01-02 09:00:00','2012-01-03 19:00:00' union all
select '2012-01-04 06:00:00','2012-01-04 23:00:00' union all
select '2012-01-05 06:00:00','2012-01-06 23:00:00' union all
select '2012-01-06 23:00:00','2012-01-07 23:00:00' union all--
select '2012-01-08 06:00:00','2012-01-09 06:00:00' union all
select '2012-01-20 23:00:00','2012-01-22 06:00:00' union all
select '2012-01-09 06:00:00','2012-01-09 19:00:00' union all
select '2012-01-10 06:00:00','2012-01-11 19:00:00' union all
select '2012-01-13 23:00:00','2012-01-14 19:00:00' union all
select '2012-01-15 09:00:00','2012-01-15 22:00:00' union all
select '2012-01-16 09:00:00','2012-01-17 22:00:00' union all
select '2012-01-18 09:00:00','2012-01-19 06:00:00'
select *,
case
when (DATEPART(HH,begindate) between 8 and 20) and (DATEPART(HH,enddate) between 8 and 20)
then (DATEDIFF(HH,begindate,enddate)-DATEDIFF(DD,begindate,enddate)*12)
when (DATEPART(HH,begindate) between 0 and 7) and (DATEPART(HH,enddate) between 8 and 20)
then (DATEDIFF(HH,convert(varchar(10),begindate,120)+' 08:00:00',enddate)-DATEDIFF(DD,begindate,enddate)*12)
when (DATEPART(HH,begindate) between 8 and 20) and (DATEPART(HH,enddate) between 21 and 23)
then (DATEDIFF(HH,begindate,convert(varchar(10),enddate,120)+' 20:00:00'))-(DATEDIFF(DD,begindate,enddate)*12)
when (DATEPART(HH,begindate) between 0 and 7) and (DATEPART(HH,enddate) between 0 and 7)
then (DATEDIFF(HH,convert(varchar(10),begindate,120)+' 08:00:00',convert(varchar(10),enddate,120)+' 20:00:00'))
-((DATEDIFF(DD,begindate,enddate)+1)*12)
when (DATEPART(HH,begindate) between 0 and 7) and (DATEPART(HH,enddate) between 21 and 23)
then (DATEDIFF(HH,convert(varchar(10),begindate,120)+' 08:00:00',convert(varchar(10),enddate,120)+' 20:00:00'))
-(DATEDIFF(DD,begindate,enddate)*12)
when (DATEPART(HH,begindate) between 21and 23) and (DATEPART(HH,enddate) between 21 and 23)
then (DATEDIFF(HH,dateadd(dd,1,convert(varchar(10),begindate,120)+' 08:00:00'),convert(varchar(10),enddate,120)+' 20:00:00'))
-((DATEDIFF(DD,begindate,enddate)-1)*12)
when (DATEPART(HH,begindate) between 8 and 20 and (DATEPART(HH,enddate) between 0 and 8))
then DATEDIFF(HH,begindate,dateadd(dd,-1,convert(varchar(10),enddate,120)+' 20:00:00'))
-((DATEDIFF(DD,begindate,enddate)-1)*12)
when (DATEPART(HH,begindate) between 21 and 23 and (DATEPART(HH,enddate) between 8 and 20))
then (DATEDIFF(HH,dateadd(dd,1,convert(varchar(10),begindate,120)+' 08:00:00'),enddate))
-((DATEDIFF(DD,begindate,enddate)-1)*12)
when (DATEPART(HH,begindate) between 21 and 23 and (DATEPART(HH,enddate) between 0 and 7))
then DATEDIFF(HH,dateadd(dd,1,convert(varchar(10),begindate,120)+' 08:00:00'),
dateadd(dd,-1,convert(varchar(10),enddate,120)+' 20:00:00'))
-((DATEDIFF(DD,begindate,enddate)-2)*12)
else 0 end as haoshi
from tbl
/*
begindate enddate haoshi
2012-01-01 09:00:00.000 2012-01-01 19:00:00.000 10
2012-01-02 09:00:00.000 2012-01-03 19:00:00.000 22
2012-01-04 06:00:00.000 2012-01-04 23:00:00.000 12
2012-01-05 06:00:00.000 2012-01-06 23:00:00.000 24
2012-01-06 23:00:00.000 2012-01-07 23:00:00.000 12
2012-01-08 06:00:00.000 2012-01-09 06:00:00.000 12
2012-01-20 23:00:00.000 2012-01-22 06:00:00.000 12
2012-01-09 06:00:00.000 2012-01-09 19:00:00.000 11
2012-01-10 06:00:00.000 2012-01-11 19:00:00.000 23
2012-01-13 23:00:00.000 2012-01-14 19:00:00.000 11
2012-01-15 09:00:00.000 2012-01-15 22:00:00.000 11
2012-01-16 09:00:00.000 2012-01-17 22:00:00.000 23
2012-01-18 09:00:00.000 2012-01-19 06:00:00.000 11
*/
我能想到的情况处理完了，你可以测试一下，有问题告诉我
hlmg789 2012-03-15
- 打赏
- 举报
ORACLE 环境哪位高手帮我看看
hlmg789 2012-03-15
- 打赏
- 举报
现在允许任何人加我为好友了，请加我联系
开着拖拉机泡妞 2012-03-15
- 打赏
- 举报
[Quote=引用 22 楼 hlmg789 的回复:]
是不是环境不一样 我是在PL/SQL环境下执行的
[/Quote]
反正我这可以执行，而且情况我考虑的差不多了，昨晚太晚了，就没考虑有的情况能不能合并
估计还有那么一两种情况没考虑进来
是不是环境不一样 我是在PL/SQL环境下执行的
[/Quote]
反正我这可以执行，而且情况我考虑的差不多了，昨晚太晚了，就没考虑有的情况能不能合并
估计还有那么一两种情况没考虑进来
hlmg789 2012-03-15
- 打赏
- 举报
是不是环境不一样 我是在PL/SQL环境下执行的
hlmg789 2012-03-15
- 打赏
- 举报
你写的这个我只要替换表名、开始时间和结束时间这个两个字段就可以了吧 ？ 不能执行
hlmg789 2012-03-14
- 打赏
- 举报
好像有问题 谁能帮帮我
Felixzhaowenzhong 2012-03-14
- 打赏
- 举报
跨天 不够12小时的忘 乘以 天数了。这里再补充一下
declare @tb table (
id int,
begindate datetime,
enddate datetime
)
insert into @tb
select 1,'2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select 2,'2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select 3,'2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select 4,'2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select 5,'2012-03-21 08:56:00.000','2012-03-24 09:56:00.000' union all
select 6,'2012-03-21 08:56:00.000','2012-03-21 09:56:00.000' union all
select 7,'2012-03-23 22:56:00.000','2012-03-25 23:56:00.000'
select
id,case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60*DATEDIFF(day,begindate,enddate) end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60*DATEDIFF(day,begindate,enddate) end
else 0 end logdate
from @tb
/*
1 720
2 1560
3 720
4 1980
5 2220
6 60
7 1560
*/
select
max(case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60*DATEDIFF(day,begindate,enddate) end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60*DATEDIFF(day,begindate,enddate) end
else 0 end) logdate
from @tb
/*
logdate
2220
*/
Felixzhaowenzhong 2012-03-14
- 打赏
- 举报
前俩个考虑情况不完整。这个是所有情况
declare @tb table (
id int,
begindate datetime,
enddate datetime
)
insert into @tb
select 1,'2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select 2,'2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select 3,'2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select 4,'2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select 5,'2012-03-21 08:56:00.000','2012-03-24 09:56:00.000' union all
select 6,'2012-03-21 08:56:00.000','2012-03-21 09:56:00.000' union all
select 7,'2012-03-23 22:56:00.000','2012-03-25 23:56:00.000'
select
id,case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
else 0 end logdate
from @tb
/*
id logdate
1 720
2 1560
3 720
4 1980
5 2220
6 60
7 1500
*/
select
max(case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
else 0 end) logdate
from @tb
/*
logdate
2220
*/
开着拖拉机泡妞 2012-03-14
- 打赏
- 举报
[Quote=引用 15 楼 hlmg789 的回复:]
能不能加我QQ联系 5867850
[/Quote]
what is your name ?
能不能加我QQ联系 5867850
[/Quote]
what is your name ?hlmg789 2012-03-14
- 打赏
- 举报
能不能加我QQ联系 5867850
Felixzhaowenzhong 2012-03-14
- 打赏
- 举报
declare @tb table (
id int,
begindate datetime,
enddate datetime
)
insert into @tb
select 1,'2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select 2,'2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select 3,'2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select 4,'2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select 5,'2012-03-21 08:56:00.000','2012-03-24 09:56:00.000' union all
select 6,'2012-03-21 08:56:00.000','2012-03-21 09:56:00.000'
select
id,case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*abs(DATEPART(hour,begindate)-DATEPART(hour,enddate)) end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*abs(DATEPART(hour,begindate)-DATEPART(hour,enddate)) end
else 0 end logdate
from @tb
/*
id logdate
1 720
2 0
3 720
4 18
5 3
6 60
*/
Felixzhaowenzhong 2012-03-14
- 打赏
- 举报
declare @tb table (
begindate datetime,
enddate datetime
)
insert into @tb
select '2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select '2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select '2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select '2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select '2012-03-21 08:56:00.000','2012-03-24 09:56:00.000' union all
select '2012-03-21 08:56:00.000','2012-03-21 09:56:00.000'
select MAX(date)max_long from (
select case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*abs(DATEPART(hour,begindate)-DATEPART(hour,enddate)) end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*abs(DATEPART(hour,begindate)-DATEPART(hour,enddate)) end
else 0 end date
from @tb )a
/*
max_long
720
*/
开着拖拉机泡妞 2012-03-14
- 打赏
- 举报
你把你哪的所有情况下的数据都给我发一条吧，我怕想不全面
hlmg789 2012-03-14
- 打赏
- 举报
太谢谢了
开着拖拉机泡妞 2012-03-14
- 打赏
- 举报
那你就用开始时间跟结束时间做差，然后再减掉开始时间和第二天早上8:00这段时间之间的小时就好了，也就是多加一个when，我的吃饭去了，一会回来给你写一个吧
hlmg789 2012-03-14
- 打赏
- 举报
有可能
开着拖拉机泡妞 2012-03-14
- 打赏
- 举报
[Quote=引用 7 楼 hlmg789 的回复:]
有没有详细的东西 如果描述不清楚 我可以再描述
[/Quote]
我想知道你的开始时间会不会在20：00到次日8:00之间？？
有没有详细的东西 如果描述不清楚 我可以再描述
[/Quote]
我想知道你的开始时间会不会在20：00到次日8:00之间？？
hlmg789 2012-03-14
- 打赏
- 举报
有没有详细的东西 如果描述不清楚 我可以再描述
加载更多回复（6）
