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:
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.