Archives of the TeradataForum

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

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

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>>