关于时间段查询

来源：互联网发布：c语言字节数组编辑：程序博客网时间：2024/05/22 12:49

(
select *
from overtime_wfrep t
where replace(substr(overtime_start,1,10),'-','')>='20071001'---175
and   replace(substr(overtime_end,1,10),'-','')<='20071003'  --可以包含录错的()
)
minus
(
select *
from overtime_wfrep t
wherereplace(substr(overtime_end,1,10),'-','')>='20071001'   --不包括录错的情况也就是（开始时间大于结束时间的)
and  replace(substr(overtime_start,1,10),'-','')<='20071003' --
)--267

select *
from overtime_wfrep
where
replace(substr(overtime_start,1,10),'-','')  between  '20071001'  and   '20071003'
or
replace(substr(overtime_end,1,10),'-','')  between  '20071001'  and   '20071003'
or
'20071001'  between  replace(substr(overtime_start,1,10),'-','')  and  replace(substr(overtime_end,1,10),'-','')
or
'20071003'  between  replace(substr(overtime_start,1,10),'-','')  and  replace(substr(overtime_end,1,10),'-','')
or
'20071001'  between  replace(substr(overtime_end,1,10),'-','')  and  replace(substr(overtime_start,1,10),'-','')
or
'20071003'  between  replace(substr(overtime_end,1,10),'-','')  and  replace(substr(overtime_start,1,10),'-','')

--返回274行包括可以查询出20071005到20070905录错的情况

0 0