title: 2665 - Invalid Date Error in Teradata
body: <p>I'm trying to complete an insert into statement but keep getting a "Invalid Date" error as a result.  </p>
  
  <p><strong>My create statement:</strong></p>
  
  <pre><code>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 );
  </code></pre>
  
  <p><strong>My insert statement:</strong></p>
  
  <pre><code>INSERT INTO Date_Table
  select
    a.customer_field,
    a.CAST (date_field AS DATE FORMAT 'yyyy-mm-dd'),
    a.other_fields
  from a
  </code></pre>
  
  <p><strong>What I've tried so far:</strong></p>
  
  <ul>
  <li>Making the date_field null by <code>cast(null as date) as date_field</code></li>
  <li>checking each date in the <code>date_field</code> against <code>sys_calendar.calendar</code> to make sure they're valid dates</li>
  <li>Checking the type by <code>SELECT TYPE (date_field) FROM date_table GROUP BY 1</code>, they all turn out to be the 'date' type</li>
  </ul>
  
  <p>Any thoughts?</p>