Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 29 Oct 2012 @ 17:26:50 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   OVERLAPS question
 
From:   de Wet, Johannes

I noticed something unusual about the OVERLAPS function that I thought was unusual. I may be miss-interpreting it's use, but would have thought that if ANY DAY in two date ranges corresponds, that the OVERLAPS function would return TRUE?

The only difference between the two queries below is the beginning period dates. Both queries 'overlap' by 1 day. I would have expected the first Query to return the text 'Overlaps' as well..

It works OK if the overlap is by more than one day (of course), or if the first date range is 1 day long and equal to the beginning period of the second range, like in the second example below. The odd thing is that both queries have an 'overlap' of 1 day, but the first doesn't considers it an overlap and the second does.

--Doesn't Overlap?

     SEL
         CASE
             WHEN ( CAST('2012-02-23' AS DATE FORMAT 'YYYY-MM-DD')
                   ,CAST('2012-02-24' AS DATE FORMAT 'YYYY-MM-DD') )
             OVERLAPS
                   ( CAST('2012-02-24' AS DATE FORMAT 'YYYY-MM-DD')
                    ,CAST('2012-10-24' AS DATE FORMAT 'YYYY-MM-DD') )
         THEN 'Overlaps'
         ELSE 'Doesnt Overlap'
         END ;

--Overlaps OK

     SEL
         CASE
             WHEN ( CAST('2012-02-24' AS DATE FORMAT 'YYYY-MM-DD')
                   ,CAST('2012-02-24' AS DATE FORMAT 'YYYY-MM-DD') )
             OVERLAPS
                   ( CAST('2012-02-24' AS DATE FORMAT 'YYYY-MM-DD')
                    ,CAST('2012-10-24' AS DATE FORMAT 'YYYY-MM-DD') )
         THEN 'Overlaps'
         ELSE 'Doesnt Overlap'
         END ;

Thanks!

Johannes de Wet
Unum



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016