2665 Invalid Date | Tried a few things to no avail

General

2665 Invalid Date | Tried a few things to no avail

I'm trying to complete an insert into statement but keep getting a "Invalid Date" error as a result.

My create statement:

CREATE MULTISET TABLE Date_Table,    NO FALLBACK ,    NO BEFORE JOURNAL,    NO AFTER JOURNAL,    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
     (      customer_field,      date_field DATE FORMAT 'YYYY-MM-DD',      other_fields
     )
PRIMARY INDEX ( date_field );

My insert statement:

INSERT INTO Date_Tableselect  a.customer_field,  a.CAST (date_field AS DATE FORMAT 'yyyy-mm-dd'),  a.other_fieldsfrom a

What I've tried so far:

  • Making the date_field null by cast(null as date) as date_field
  • checking each date in the date_field against sys_calendar.calendar to make sure they're valid dates
  • Checking the type by SELECT TYPE (date_field) FROM date_table GROUP BY 1, they all turn out to be the 'date' type

Any thoughts?

2 REPLIES
Teradata Employee

Re: 2665 Invalid Date | Tried a few things to no avail

What data type and format is "a.datefield"? This says that the data in one or more rows in that column does not match the format specified. Or that it has invalid contents for a field in the format (like month 14 or day 30 for Feb).

Re: 2665 Invalid Date | Tried a few things to no avail

It's date 'yyyy-mm-dd' format.  I've ran a query checking each element 

 

I tested the first possibility by running the below and got no results:

 

SELECT TYPE (date_field) FROM table GROUP BY 1

 

I tested the second possibilty by checking against sys_calendar.calendar with no results:

 

SEL a.date_field 
FROM table a
LEFT JOIN sys_calendar.calendar b
ON a.date_field = b.calendar_date 
WHERE a.date_field IS NULL

 

I can run a simple select statement and even create a volatile table on the same exact code but for some reason the insert into doesn't work.