如何用SQL剔除夜间时间

Question

有一个表 有两列 开始时间 结束时间   需要知道结束时间减去开始时间看用了多长时间  但是要剔除晚上20:00到早上8:00的这段时间   开始时间和结束时间有可能不是同一天，也有可能是同一天 谁有办法用SQL实现这个东西  帮帮忙 谢谢了

Answer 1

go
create table #m(
begindate datetime,
enddate datetime
)
go
insert #m
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'select *,(DATEDIFF(HH,begindate,enddate)-DATEDIFF(DD,begindate,enddate)*12) as haoshi
from #m/*
begindate enddate haoshi
2012-01-12 08:56:00.000 2012-01-13 08:56:00.000 12
2012-01-14 08:56:00.000 2012-01-16 06:56:00.000 22
2012-02-12 08:56:00.000 2012-02-13 09:23:00.000 13
2012-03-15 08:56:00.000 2012-03-17 17:04:00.000 33
2012-03-21 08:56:00.000 2012-03-24 09:56:00.000 37
*/

Answer 2

--如果要精确到分
select *,(cast(DATEDIFF(MI,begindate,enddate)as numeric(10,2))/60)
-(DATEDIFF(DD,begindate,enddate)*12) as haoshi from #m/*
begindate enddate haoshi
2012-01-12 08:56:00.000 2012-01-13 08:56:00.000 12.000000
2012-01-14 08:56:00.000 2012-01-16 06:56:00.000 22.000000
2012-02-12 08:56:00.000 2012-02-13 09:23:00.000 12.450000
2012-03-15 08:56:00.000 2012-03-17 17:04:00.000 32.133333
2012-03-21 08:56:00.000 2012-03-24 09:56:00.000 37.000000
*/

Answer 3

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([begindate] datetime,[enddate] datetime)
insert [tbl]
select '2012-02-04 18:00','2012-02-05 9:00' union all
select '2012-02-04 15:00','2012-02-04 22:00' union all
select '2012-02-04 18:00','2012-02-05 07:00' union all
select '2012-02-05 8:00','2012-02-05 23:00'--如果要精确到分
select *,
case when day([begindate])<>day([enddate]) and DATEPART(HH,[enddate])>=8
then (DATEDIFF(HH,begindate,enddate))-(DATEDIFF(DD,begindate,enddate)*12)
when day([begindate])<>day([enddate]) and DATEPART(HH,[enddate])<8
or (day([begindate])=day([enddate]) and DATEPART(HH,[enddate])>=20)
then datediff(HH,begindate,convert(varchar(10),begindate,120)+' 20:00:00')
else DATEDIFF(HH,begindate,enddate)
end as haoshi
from tbl/*
begindate enddate haoshi
2012-02-04 18:00:00.000 2012-02-05 09:00:00.000 3
2012-02-04 15:00:00.000 2012-02-04 22:00:00.000 5
2012-02-04 18:00:00.000 2012-02-05 07:00:00.000 2
2012-02-05 08:00:00.000 2012-02-05 23:00:00.000 12
*/你可以用case when 把所有情况下的算法列出来，然后看看有没有能合并的，在合并就好了

Answer 4

那你就用开始时间跟结束时间做差，然后再减掉开始时间和第二天早上8:00这段时间之间的小时就好了，也就是多加一个when，我的吃饭去了，一会回来给你写一个吧

Answer 5

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
*/

Answer 6

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
*/

Answer 7

what is your name ?

Answer 8

前俩个考虑情况不完整。这个是所有情况
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
*/

Answer 9

跨天 不够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
*/

Answer 10

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
*/我能想到的情况处理完了，你可以测试一下，有问题告诉我

Answer 11

是不是环境不一样 我是在PL/SQL环境下执行的

Answer 12

ORACLE 环境哪位高手帮我看看

Answer 13

ORACLE 出门左拐第一个胡同问问吧