I am working with Netezza SQL.
I have the two following tables:
- table_1 and table_2
- table_1 has a date variable "Date_1" that has a variable type "character_varrying(255)"
- table_2 has two date variables "Date_2" and "Date_3" that have variable types "DATE"
- All 3 dates have the same form : 2010-12-31
I am trying to run the following SQL command:
CREATE TABLE final_table AS SELECT * FROM table_1 a INNER JOIN table_2 b ON (( a.date_1 BETWEEN b.date_2 AND b.date_3) AND a.id = b.id) OR (a.id1 = b.id1)
Problem: But this gives me the following error:
HY000 Error: Bad External Date Representation
What I tried so far: I am trying to figure out how to make all DATE variables compatible so that the INNER JOIN works. I thought that maybe this can be done using the ALTER TABLE command:
#create new date variable ALTER TABLE table_1 ADD COLUMN new_date DATE; #set new date variable UPDATE table_1; set new_date = date_1; #repeat inner join with new date variable CREATE TABLE final_table AS SELECT * FROM table_1 a INNER JOIN table_2 b ON (( a.new_date BETWEEN b.date_2 AND b.date_3) AND a.id = b.id) OR (a.id1 = b.id1)
But the same error is still persisting.
Can someone please show me how to fix this?