advanced code snippet search

Posted By

mprabhuram on 06/04/10


Tagged

sql Oracle SmartySQL


Versions (?)

Oracle - Overlaps function

 / Published in: SQL
 

URL: http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/

This function can be used to find out if there exists an overlap between two pairs of dates or time.

There is an alternate way to calculate the actual overlapping value - Read the snippet "How to avoid double counts"

Expand | Embed | Plain Text 
  1. -- Overlap present
  2.  
  3. SELECT 'YES' AS overlap FROM dual
  4.     WHERE          (date '2007-01-01', date '2008-01-01')
  5.           overlaps (date '2005-01-01', date '2009-01-01')
  6.  
  7.  
  8. OVE
  9. ---
  10. YES
  11.  
  12. -- Overlap not present
  13.  
  14. SELECT 'YES' AS overlap FROM dual
  15.     WHERE          (date '2007-01-01', date '2008-01-01')
  16.           overlaps (date '2005-01-01', date '2006-01-01')
  17.  
  18.  
  19. no rows selected.

Report this snippet  

You need to login to post a comment.