sqldateinner-joinnetezza

SQL : Inner Joins with Different Date Formats


I have the two following tables:

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?


Solution

  • 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)