SELECT Failed 2665: Invalid Date

Database
Enthusiast

SELECT Failed 2665: Invalid Date

Hi

Simply I am trying to return data between two date extremities.  The entity used is a timestamp and I have converted it to a date, but I this SELECT Failed 2665 error persists.  The only thing that I can think would cause this error would be the NULL values in some of the records.  Could that be the problem?  Is there an equivalent to the nz(Entity_name) in Access in Teradata?

WHERE
                         CAST(A.LST_UPDT_TMSTMP AS DATE)
                         BETWEEN
                         '01/01/2013'    -- REPORTING PERIOD START DATE
                         AND
                         '01/01/2014'   -- REPORTING PERIOD END DATE

Thanks

2 REPLIES
Teradata Employee

Re: SELECT Failed 2665: Invalid Date

The error message is correct. Those are character literals that are not in a valid date format.

You should use DATE literals instead: BETWEEN DATE '2013-01-01' AND DATE '2014-01-01'

Enthusiast

Re: SELECT Failed 2665: Invalid Date

I guess there is a UDF ISDATE(), which returns the valid date or null if it is invalid. You can use that to test the data in datecolumn you are working with.