Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Between: not working as expected
From:   Victor Sokovin

          > Select *
          > From dbc.dbcinfo;
          > Output
          > VERSION
          > RELEASE V2R.
          > ------------------------------------------------------
          > sel *
          > 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.



  <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