Netezza SQL: Joins with Different Date Formats

1

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?

Thanks!

2

hi

Hope this helps

one of the very common errors is
the data types of what you are comparing needs to be the same

apples can only be compared with apples
oranges can only be compared with oranges

date_1 date_2 date_3 should all be the same data type

typically if they are are NOT then you cast or convert into that data type

date_2 and date_3 should be converted to date data type and then the between should work

1 Like