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

xiexue202 2017-01-05 05:36:51
例如：sqlserver '2017-01-02 02:02:21.287' 和 '2017-01-05 08:00:21.287' 两个数据。
我要获取这两个数据在08：00至20：00时间段中一共有多少小时。
求大牛帮忙
...全文
4260 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
baidu_35289351 2017-01-06
  • 打赏
  • 举报
 回复
baidu_35289351 2017-01-06
  • 打赏
  • 举报
 回复
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
中国风 2017-01-06
  • 打赏
  • 举报
 回复
引用 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
*/
baidu_35289351 2017-01-06
  • 打赏
  • 举报
 回复
用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小时才是。也不对
baidu_35289351 2017-01-06
  • 打赏
  • 举报
 回复
baidu_35289351 2017-01-06
  • 打赏
  • 举报
 回复
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
中国风 2017-01-06
  • 打赏
  • 举报
 回复
改改，比如下方 结束时间为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
*/
xiexue202 2017-01-06
  • 打赏
  • 举报
 回复
如果按分钟计算是不是准确一点？
中国风 2017-01-06
  • 打赏
  • 举报
 回复
引用 6 楼 xiexue202 的回复:
这个函数有点问题。算不准确
比如？ 只计算每天的8:00~20:00这个时间 段 比如：开始时间推迟 为9:00会减1小时，结束时间提前 为19：00会减1小时 都以小时数为单位，要精准需要按分钟计算，又会有新问题按小时是否把小时换算为小数
xiexue202 2017-01-06
  • 打赏
  • 举报
 回复
这个函数有点问题。算不准确
baidu_35289351 2017-01-06
  • 打赏
  • 举报
 回复
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
baidu_35289351 2017-01-06
  • 打赏
  • 举报
 回复
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' 多
xiexue202 2017-01-06
  • 打赏
  • 举报
 回复
谢谢。厉害了。不知道咋学sql能变这么精通
wwfxgm 2017-01-05
  • 打赏
  • 举报
 回复
好长的sql代码呀。啃啃
中国风 2017-01-05
  • 打赏
  • 举报
 回复
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
*/
SQL自定义函数，计算日期间隔，不包含（除去）周末和下班时间
SQL自定义函数，计算日期间隔，不包含（除去）周末和下班时间. 上班时间：8：30 ~ 17:30，不包含周六周日。
时间段互斥算法SQL实现
最近有接到需求，线上需要创建一个活动信息，同一时间段只能生效一个活动，活动状态需要自动流转。本文不针对全部需求进行设计，只讨论时间互斥的实现 实现思路 当前需要写入时间段S1----E1，场景模型建立： 表中的...
预定求一个时间段内可预约的房间数
sql计算每一天的可用房间数，可预约房间数最少的一天就是这个时间段内可预约的房间数 select <if test="null != dateList and dateList.size > 1"> LEAST( <foreach collection="dateList" index=...
SQL求最高在线人数以及最高峰时间段
（2）出现最高峰的时间段是哪个时间? 建表 注：我用的oracle实现的 DROP TABLE "STARPOWER"."PLAY"; CREATE TABLE "STARPOWER"."PLAY" ( "ID" VARCHAR2(100 BYTE), "SDT" DATE, "EDT" DATE ) ...
重叠时间段问题优化算法详解
问题提出、优化重叠查询、改进取得活跃时段的算法、MySQL 8 的单条查询解决方案
community_281

662

社区成员

253,727

社区内容

发帖
与我相关
我的任务
社区描述
提出问题
其他 技术论坛（原bbs）
社区管理员
  • community_281
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章