Teradata - Invalid Date supplied for FIELD

Question

Hi all,

I'm trying to query a table that has a varchar(100) "VALUE" column. This column can hold anything from a letter, a number or, in this case, a date.

The date will always be entered in the table as 'YYYY-mm-dd'. However, when I run the following query:

select * from myTable
where VALUE =  '2009-12-11' (Date, Format 'yyyy-mm-dd')  

I receive the following error:

Invalid date supplied for myTable.VALUE.

Example of the value table: (1,'122') (2,'red') (3,'2009-12-11')

Any ideas as to what might be causing this?

Thanks!

Answer 1

if the data type is declared as varchar, it should just treat it like a string. try not specifying anything about the date format, like

select * from myTable
where VALUE =  '2009-12-11'

Answer 2

If you run an explain on the query, you can see that it's casting value to date before comparing against your supplied value. If you have another column that accurately records the type of what's in VALUE, you can add that to the where clause and you will no longer get the error (see below). Otherwise, go with Beth's recommendation.

select * from myTable
where VALUE =  '2009-12-11' (Date, Format 'yyyy-mm-dd')
and VALUE_TYPE = 'DATE';