Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 31 Dec 2010 @ 15:18:22 GMT


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


Subj:   Re: Odd case with Dates
 
From:   Dieter Noeth

Deepak Agrawal wrote:

  Expression DATE+0 is converting date data type to integer and then performing comparison.  


No, it's not, it simply adds zero days.


  You can check the difference by executing below query on your SQL assistant.  


  Sel date(integer),date+0;  


  The output is 1,101,231 and 12/31/2010  


I can't see any typecast for date+0. And the DATE+0 version returns the correct result set whereas DATE doesn't.


Maria,

you're correct, both queries should return the same result. If you look at explain you'll see a derived condition added by the optimizer, which is plain wrong:

     3) We do an all-AMPs SUM step to aggregate from
        TERADATA_EDUCATION.DWH_DIA by way of an all-rows scan with a
        condition of ("(TERADATA_EDUCATION.DWH_DIA.fec_dia < DATE
        '2010-12-31') AND (((EXTRACT(YEAR FROM
        (TERADATA_EDUCATION.DWH_DIA.fec_dia )))>= 2009) AND ((EXTRACT(YEAR
        FROM (TERADATA_EDUCATION.DWH_DIA.fec_dia )))< 2010 ))").

The last condition is missing when you explain the DATE version. This is definitely a bug, you should open an incident.

Btw, isn't id_anyo supposed to be the year? Then it's easier (and the correct answer):

     WHERE
        id_anyo >= (EXTRACT(YEAR FROM DATE))-1
        AND fec_dia


Dieter



     
  <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: 15 Jun 2023