求一个Sql日期算法求某时间段内自定义小时间段包含小时数

例如：sqlserver '2017-01-02 02:02:21.287'   和  '2017-01-05 08:00:21.287' 两个数据。
我要获取这两个数据在08：00至20：00时间段中一共有多少小时。
求大牛帮忙

15 个解决方案

#1

e.g.

DECLARE @StartD DATETIME= '2017-01-02 02:02:21.287' ,
    @EndD DATETIME= '2017-01-05 08:00:21.287' ,
    @dd INT ,
    @hh INT ,
    @StartT DATETIME= '08:00' ,
    @EndT DATETIME= '20:00';


SELECT  @dd = DATEDIFF(dd, @StartD, @EndD) ,
        @hh = DATEPART(hh, @EndT) - DATEPART(hh, @StartT) ,
        @hh = @hh * ( @dd + 1 )
        + CASE WHEN @StartD > DATEADD(hh, DATEPART(hh, @StartT),
                                      CONVERT(VARCHAR(10), @StartD, 120))
               THEN DATEDIFF(hh, @StartD,
                             DATEADD(hh, @hh,
                                     CONVERT(VARCHAR(10), @StartD, 120)))
               ELSE 0
          END
        + CASE WHEN @EndD < DATEADD(hh, DATEPART(hh, @EndT),
                                    CONVERT(VARCHAR(10), @EndD, 120))
               THEN DATEDIFF(hh,
                             DATEADD(hh, DATEPART(hh, @EndT),
                                     CONVERT(VARCHAR(10), @EndD, 120)), @EndD)
               ELSE 0
          END;
SELECT  @hh;

/*
36
*/

#2

 好长的sql代码呀。啃啃

#3

谢谢。厉害了。不知道咋学sql能变这么精通

#4

set @StartD = '2017-01-02 07:02:21.287'
set @EndD = '2017-01-03 07:00:21.287'
算的结果是11小时，

set @StartD = '2017-01-02 08:02:21.287'
set @EndD = '2017-01-03 07:00:21.287'
算的结果是15小时，

理论上，7点比8点前，'2017-01-02 07:02:21.287' 至  '2017-01-03 07:00:21.287'
肯定会比 '2017-01-02 08:02:21.287' 至  '2017-01-03 07:00:21.287' 多

#5

set @StartD = '2017-01-02 08:00:00.00'
set @EndD   = '2017-01-03 08:00:00.00'
结果 12，对
set @StartD = '2017-01-02 08:00:00.00'
set @EndD   = '2017-01-03 09:00:00.00'
结果 13，对
set @StartD = '2017-01-02 09:00:00.00'
set @EndD   = '2017-01-03 09:00:00.00'
结果 16，错，应该是12

#6

这个函数有点问题。算不准确

#7

引用 6 楼 xiexue202 的回复:

这个函数有点问题。算不准确

比如？

只计算每天的8:00~20:00这个时间 段

比如：开始时间推迟 为9:00会减1小时，结束时间提前 为19：00会减1小时
都以小时数为单位，要精准需要按分钟计算，又会有新问题按小时是否把小时换算为小数

#8

如果按分钟计算是不是准确一点？

#9

改改，比如下方 结束时间为06:00不在08：00~20：00范围，01-05当天的小时数不计算 

e.g.

DECLARE @StartD DATETIME= '2017-01-02 02:02:21.287' ,
    @EndD DATETIME= '2017-01-05 06:00:21.287' ,
    @dd INT ,
    @hh INT ,
    @StartT DATETIME= '08:00' ,
    @EndT DATETIME= '20:00';


SELECT  @dd = DATEDIFF(dd, @StartD, @EndD) ,
        @hh = DATEPART(hh, @EndT) - DATEPART(hh, @StartT) ,
        @hh = @hh * ( @dd + 1 )
        + CASE WHEN @StartD > DATEADD(hh, DATEPART(hh, @StartT),
                                      CONVERT(VARCHAR(10), @StartD, 120))
               THEN DATEDIFF(hh, @StartD,
                             DATEADD(hh, @hh,
                                     CONVERT(VARCHAR(10), @StartD, 120)))
               ELSE 0
          END
        + CASE WHEN @EndD < DATEADD(hh, DATEPART(hh, @EndT),
                                    CONVERT(VARCHAR(10), @EndD, 120))
               THEN CASE WHEN @StartD > DATEADD(hh, DATEPART(hh, @EndT),
                                                CONVERT(VARCHAR(10), @EndD, 120))
                         THEN DATEDIFF(hh,
                                       DATEADD(hh, DATEPART(hh, @EndT),
                                               CONVERT(VARCHAR(10), @EndD, 120)),
                                       @EndD)
                         ELSE -@hh
                    END
               ELSE 0
          END;
SELECT  @hh;

/*
36
*/

#10

DECLARE @StartD DATETIME ,
    @EndD DATETIME ,
    @StartT DATETIME ,
    @EndT DATETIME ,
    @dd INT ,
    @hh INT 
 
set @StartD = '2017-01-02 9:00:00.00'
set @EndD   = '2017-01-03 19:00:00.00'

set @StartT = '8:00' 
set @EndT  = '20:00'

set @hh = 0
declare @NewStartT datetime,@NewEndT datetime,@dateS datetime,@dateE datetime
if @EndD <= @StartD
begin 
   set @hh = 0
end
else
begin
   set @dd= DATEDIFF(dd,@StartD,@EndD)
   while @dd>=0
   begin 
      set @NewStartT = substring(convert(varchar(23), @StartD, 121),1,11) + substring(convert(varchar(23), @StartT, 121),12,12)
      set @NewEndT = substring(convert(varchar(23), @StartD, 121),1,11) + substring(convert(varchar(23), @EndT, 121),12,12)      
      --判断开始日期大还是开始时间大
      if @StartD > @NewStartT set @dateS = @StartD
      else  set @dateS = @NewStartT

      if (@EndD > @NewEndT or @dd <> 0)set @dateE = @NewEndT
      else  set @dateE = @EndD
      
      select @dd as 天,@dateS as 开始,@dateE as 结束,DATEDIFF(hh,@dateS,@dateE)as 小时  --观察每天运算结果，可以去掉
      set @hh = @hh + DATEDIFF(n,@dateS,@dateE)  --精确到小时 DATEDIFF(hh,@dateS,@dateE),结果 select @hh

   set @dd=@dd-1
   set @StartD=dateadd(day,1,@NewStartT)
   end
end

select @hh/60.00

#11

#12

用9楼算法，当
set @StartD = '2017-01-02 9:00:00.00'
set @EndD   = '2017-01-03 19:00:00.00'

set @StartT = '8:00' 
set @EndT  = '20:00'
结果15小时，但应该是22小时才是。也不对

#13

引用 12 楼 baidu_35289351 的回复:

用9楼算法，当
set @StartD = '2017-01-02 9:00:00.00'
set @EndD   = '2017-01-03 19:00:00.00'

set @StartT = '8:00' 
set @EndT  = '20:00'
结果15小时，但应该是22小时才是。也不对

用以下测测，漏改一个位置@hh

DECLARE @StartD DATETIME= '2017-01-02 09:02:21.287' ,
    @EndD DATETIME= '2017-01-03 19:00:21.287' ,
    @dd INT ,
    @hh INT ,
    @StartT DATETIME= '08:00' ,
    @EndT DATETIME= '20:00';


SELECT  @dd = DATEDIFF(dd, @StartD, @EndD) ,
        @hh = DATEPART(hh, @EndT) - DATEPART(hh, @StartT),
        @hh = @hh * ( @dd + 1 )
        + CASE WHEN @StartD > DATEADD(hh, DATEPART(hh, @StartT),
                                      CONVERT(VARCHAR(10), @StartD, 120))
               THEN DATEDIFF(hh, @StartD,
                             DATEADD(hh, DATEPART(hh, @StartT),
                                     CONVERT(VARCHAR(10), @StartD, 120)))
               ELSE 0
          END
        + CASE WHEN @EndD < DATEADD(hh, DATEPART(hh, @EndT),
                                    CONVERT(VARCHAR(10), @EndD, 120))
               THEN CASE WHEN @StartD < DATEADD(hh, DATEPART(hh, @EndT),
                                                CONVERT(VARCHAR(10), @EndD, 120))
                         THEN DATEDIFF(hh,
                                       DATEADD(hh, DATEPART(hh, @EndT),
                                               CONVERT(VARCHAR(10), @EndD, 120)),
                                       @EndD)
                         ELSE -@hh
                    END
               ELSE 0
          END;
SELECT  @hh;

/*
22
*/

#14

13楼和我10楼的，也存在1个问题，就是负数
当
set @StartD = '2017-01-02 02:02:21.287'
set @EndD = '2017-01-05 06:00:21.287'

set @StartT = '8:00' 
set @EndT  = '20:00'
结果是 34，其实要36才是的，最后1天是-2.
我10楼，有个地方要改改
set @hh = @hh + DATEDIFF(n,@dateS,@dateE)
改为：
if DATEDIFF(n,@dateS,@dateE) >=0 set @hh = @hh + DATEDIFF(n,@dateS,@dateE)

--更正后

DECLARE @StartD DATETIME ,
    @EndD DATETIME ,
    @StartT DATETIME ,
    @EndT DATETIME ,
    @dd INT ,
    @hh INT 
 

set @StartD = '2017-01-02 02:02:21.287'
set @EndD = '2017-01-05 06:00:21.287'

set @StartT = '8:00' 
set @EndT  = '20:00'

set @hh = 0
declare @NewStartT datetime,@NewEndT datetime,@dateS datetime,@dateE datetime
if @EndD <= @StartD
begin 
   set @hh = 0
end
else
begin
   set @dd= DATEDIFF(dd,@StartD,@EndD)
   while @dd>=0
   begin 
      set @NewStartT = substring(convert(varchar(23), @StartD, 121),1,11) + substring(convert(varchar(23), @StartT, 121),12,12)
      set @NewEndT = substring(convert(varchar(23), @StartD, 121),1,11) + substring(convert(varchar(23), @EndT, 121),12,12)      
      --判断开始日期大还是开始时间大
      if @StartD > @NewStartT set @dateS = @StartD
      else  set @dateS = @NewStartT

      if (@EndD > @NewEndT or @dd <> 0)set @dateE = @NewEndT
      else  set @dateE = @EndD
      
      select @dd as 天,@dateS as 开始,@dateE as 结束,DATEDIFF(hh,@dateS,@dateE)as 小时  --观察每天运算结果，可以去掉
       if DATEDIFF(n,@dateS,@dateE)>=0 set @hh = @hh + DATEDIFF(n,@dateS,@dateE)  --精确到小时 DATEDIFF(hh,@dateS,@dateE),结果 select @hh

   set @dd=@dd-1
   set @StartD=dateadd(day,1,@NewStartT)
   end
end

select @hh/60.00

#15