关于时间段查询

  (
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
where replace(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录错的情况