SELECT * FROM FMMS_MACHINE11
WHERE
CAST('2021-07-16 15:00:00' AS DATETIME) <= DT_INSDATE
AND DT_INSDATE < DATEADD(MI, 1, CAST('2021-07-16 16:00:00' AS DATETIME))
이렇게 짯는데.. 혹시 DT_INSDATE는 2021-07-16 15:00:00~16:00:00이 데이중에 44분부터 ~16:00분까지는 데이터가 있는데
예를들어서 1분씩 데이터가 INSERT되는데 INSERT되지않은 15:00:00부터~43분까지 없는 데이터의 시간을 서치하고싶은데 어떻게 짜야할까요? 없는 데이터의 시간을 쿼리할수있게...
Comment 1
-
지영아빠
2021.07.21 09:16
test table
create table FMMS_MACHINE11 (
DT_INSDATE datetime
)
insert into FMMS_MACHINE11 (DT_INSDATE) values ('2021-07-16 00:01:10.000'), ('2021-07-16 00:02:10.000')
go
-- 대략 여기서 부터요
declare @dt datetime = '2021-07-16'
;with tb_dt as (
select @dt as d
union all
select dateadd(mi, 1, d ) from tb_dt
where dateadd(mi, 1, d ) < dateadd(dd, 1, @dt)
)
select
d as s_dt,
dateadd(mi, 1, d) as e_dt,
b.DT_INSDATE
from
tb_dt a
left outer join FMMS_MACHINE11 b
on b.DT_INSDATE between a.d and dateadd(mi, 1, a.d)
where
b.DT_INSDATE is null
option (maxrecursion 0)