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

Attachments

Library

Quick Reference