查询符合指定时间段的记录

查询符合指定时间段的记录

样例数据准备

查询要求:查询日期在每日07:30到10:00之间的所有日期的数据。

CREATE TABLE EXAM (
  OPDT DATETIME,
  SM   VARCHAR(20)
)
GO
WITH EX (opdt,SM) AS
(
  select '2019-02-01 07:29:59','不符' union all
  select '2019-02-01 07:30:00','符合' union all
  select '2019-02-01 07:30:01','符合' union all
  select '2019-02-01 09:59:59','符合' union all
  select '2019-02-01 10:00:00','符合' union all
  select '2019-02-01 10:00:01','不符' union all
  select '2019-02-02 07:29:59','不符' union all
  select '2019-02-02 07:30:00','符合' union all
  select '2019-02-02 07:30:01','符合' union all
  select '2019-02-02 09:59:59','符合' union all
  select '2019-02-02 10:00:00','符合' union all
  select '2019-02-02 10:00:01','不符' union all
  select '2019-12-01 07:29:59','不符' union all
  select '2019-12-01 07:30:00','符合' union all
  select '2019-12-01 07:30:01','符合' union all
  select '2019-12-01 09:59:59','符合' union all
  select '2019-12-01 10:00:00','符合' union all
  select '2019-12-01 10:00:01','不符' union all
  select '2019-12-02 07:29:59','不符' union all
  select '2019-12-02 07:30:00','符合' union all
  select '2019-12-02 07:30:01','符合' union all
  select '2019-12-02 09:59:59','符合' union all
  select '2019-12-02 10:00:00','符合' union all
  select '2019-12-02 10:00:01','不符'
)
INSERT INTO EXAM SELECT * FROM EX

方法一

SELECT *
from EXAM
where opdt>='2019-01-01' and opdt<='2019-12-31'
  and convert(varchar(20),opdt,108) >= '07:30:00'
  and convert(varchar(20),opdt,108) <= '10:00:00'

在这里插入图片描述

方法二

WITH EX (opdt,SM) AS
(
  select '2019-02-01 07:29:59','不符' union all
  select '2019-02-01 07:30:00','符合' union all
  select '2019-02-01 07:30:01','符合' union all
  select '2019-02-01 09:59:59','符合' union all
  select '2019-02-01 10:00:00','符合' union all
  select '2019-02-01 10:00:01','不符' union all
  select '2019-02-02 07:29:59','不符' union all
  select '2019-02-02 07:30:00','符合' union all
  select '2019-02-02 07:30:01','符合' union all
  select '2019-02-02 09:59:59','符合' union all
  select '2019-02-02 10:00:00','符合' union all
  select '2019-02-02 10:00:01','不符' union all
  select '2019-12-01 07:29:59','不符' union all
  select '2019-12-01 07:30:00','符合' union all
  select '2019-12-01 07:30:01','符合' union all
  select '2019-12-01 09:59:59','符合' union all
  select '2019-12-01 10:00:00','符合' union all
  select '2019-12-01 10:00:01','不符' union all
  select '2019-12-02 07:29:59','不符' union all
  select '2019-12-02 07:30:00','符合' union all
  select '2019-12-02 07:30:01','符合' union all
  select '2019-12-02 09:59:59','符合' union all
  select '2019-12-02 10:00:00','符合' union all
  select '2019-12-02 10:00:01','不符'
)
select *
from EX
where opdt>='2019-01-01' and opdt<='2019-12-31'
      and DATEPART(hh,opdt)*10000+DATEPART(mi,opdt)*100+DATEPART(ss,opdt) >= 073000
      and DATEPART(hh,opdt)*10000+DATEPART(mi,opdt)*100+DATEPART(ss,opdt) <= 100000

方法三

WITH EX (opdt,SM) AS
(
  select '2019-02-01 07:29:59','不符' union all
  select '2019-02-01 07:30:00','符合' union all
  select '2019-02-01 07:30:01','符合' union all
  select '2019-02-01 09:59:59','符合' union all
  select '2019-02-01 10:00:00','符合' union all
  select '2019-02-01 10:00:01','不符' union all
  select '2019-02-02 07:29:59','不符' union all
  select '2019-02-02 07:30:00','符合' union all
  select '2019-02-02 07:30:01','符合' union all
  select '2019-02-02 09:59:59','符合' union all
  select '2019-02-02 10:00:00','符合' union all
  select '2019-02-02 10:00:01','不符' union all
  select '2019-12-01 07:29:59','不符' union all
  select '2019-12-01 07:30:00','符合' union all
  select '2019-12-01 07:30:01','符合' union all
  select '2019-12-01 09:59:59','符合' union all
  select '2019-12-01 10:00:00','符合' union all
  select '2019-12-01 10:00:01','不符' union all
  select '2019-12-02 07:29:59','不符' union all
  select '2019-12-02 07:30:00','符合' union all
  select '2019-12-02 07:30:01','符合' union all
  select '2019-12-02 09:59:59','符合' union all
  select '2019-12-02 10:00:00','符合' union all
  select '2019-12-02 10:00:01','不符'
)
select *
from EX
where opdt>='2019-01-01' and opdt<='2019-12-31'
      and substring(convert(varchar(20),opdt,108),12,8)>='07:30:00'
      and substring(convert(varchar(20),opdt,108),12,8)<='10:00:00'

©️2020 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值