SQL Netezza: Inner Joins with Different Date Formats-CodePudding

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?

CodePudding user response：

This problem is specific to the data in your (varchar) table. The database is attempting to ‘implicitly’ convert from VARCHAR to DATE. You should try to find the values that are ‘wrong’

For a ‘quickfix’ to this particular problem you could try to explicitly convert the other way around: from DATE to VARCHAR:

CREATE TABLE final_table AS SELECT * FROM table_1 a INNER JOIN table_2 b ON (( a.date_1 BETWEEN VARCHAR(b.date_2,’YYYY-MM-DD’) AND VARCHAR(b.date_3,’YYYY-MM-DD’)) AND a.id = b.id) OR (a.id1 = b.id1)