Archives of the TeradataForum

Message Posted: Fri, 23 Nov 2007 @ 09:53:41 GMT

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

          > Select *
          > From dbc.dbcinfo;
          >
          > Output
          >
          > VERSION 06.01.01.63
          > RELEASE V2R.06.01.01.64
          > ------------------------------------------------------
          > sel *
          > >From    CKSPRD_VIEW.VEHICLE_OWNERSHIP_CYCLE_FACT
          > Where DISPOSAL_DATE is Null
          >
          > Result: 0 rows processed.

Thanks for the additional information. It confirms some conjectures already mentioned in the thread but the puzzle remains open.

I'd like to mention that although I agree with other Forum members that the queries might be a little careless about date formats but the interesting part is not to rewrite them but to understand why the results are different (OK, this is more of an academic interest). The only bit which needs to be corrected is the equalities for both comparison operators:

     sel *
     >From test
     Where Exit_Date Between '0001-01-01'
     And '1900-01-01'

is equivalent to

     sel *
     >From test
     Where Exit_Date =< '1900-01-01'
     And Exit_Date >= '0001-01-01'

Could you please rerun the latter for us? Your original version

     sel *
     >From test
     Where Exit_Date < '1900-01-01'
     And Exit_Date > '0001-01-01'

did not include the lower and upper bound of the date range, so formally speaking the queries should not have always returned equal outputs.

It would be interesting to see whether the results are the same now or they still differ. This is the first point. If the results still differ it's a bug. If they don't differ then this point is closed.

The second interesting point is to explain why you get blank dates in the output of the first query (the quote from the original posting "First one returns me 35725730 records with Exit_Date as blank.") despite the fact that the date column does not contain any NULLs.

For the second point we'll need more information on how you actually display the results. Which tool are you using (if ODBC/JDBC based then the ODBC/JDBC drivers may contribute to the confusion as they are known for their ability to rewrite the queries before the queries actually get submitted to Teradata. Some tools may apply conversions to the output which will further confuse the user.)?

It would be useful to see the output of the "help session" command as well. It can help with the default date formats which can shed light on how your date format conversions are working in this particular case.

Regards,

Victor

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