--結尾加上1秒,是因為此function endtime=start_time  視為未重複,
因此稍作手腳給他變一下可檢查到endtime=start_time 
 
--重複 --X  
SELECT * FROM DUAL
WHERE
(to_date('20140917','yyyymmdd') ,to_date('20140918','yyyymmdd')+1/86400)
OVERLAPS
(to_date('20140916','yyyymmdd'),to_date('20140921','yyyymmdd')+1/86400)
 
 
--沒重複--null
SELECT * FROM DUAL
WHERE
(to_date('20140901','yyyymmdd') ,to_date('20140915','yyyymmdd')+1/86400)
OVERLAPS
(to_date('20140916','yyyymmdd'),to_date('20140921','yyyymmdd')+1/86400)
 
範例 ,同一業務交辦事項時間不可重複檢查 <>0 表重複
綠色是丟進去檢查的日期參數
 
  select count(*)
   from XX_TASK_ASSIGNMENT
  where ASSIGNMENT_TYPE='SALES'   
    and ASSIGNMENT_VALUE='100000158'
    and STATUS='O'
    and ((TASK_DATE_START,nvl(TASK_DATE_END,sysdate)+1/86400)
        OVERLAPS
         (to_date('20140929','yyyymmdd'),nvl(null,sysdate)+1/86400)
        )  ;   
=0 -->ok
 
 
  select count(*)
   from XX_TASK_ASSIGNMENT
  where ASSIGNMENT_TYPE='SALES'   
    and ASSIGNMENT_VALUE='100000158'
    and STATUS='O'
    and ((TASK_DATE_START,nvl(TASK_DATE_END,sysdate)+1/86400)
        OVERLAPS
         (to_date('20140928','yyyymmdd'),nvl(null,sysdate)+1/86400)
        )  ;   
=1 -->20140928 有定義一筆結束時間
arrow
arrow
    全站熱搜

    Nolem 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄