How to join tables by date column with different date format in SQL Vertica?

Question

I have two tables in SQL

table1:

date        col2
2020-10-25  2
2020-10-20  20

table2:

date        col1
25.10.2020  1
20.10.2020  10

As you can see i have different format of date. My question is how can I in Vertica SQL join this two tables on date column in each table ?

Simply I need to join this two tables by date column co for examplle I need table with 3 columns: date, col1, col2 like below:

result:

date        col1  col2
2020-10-25  1     2
2020-10-20  10    20

question from:https://stackoverflow.com/questions/65898859/how-to-join-tables-by-date-column-with-different-date-format-in-sql-vertica

Answer 1

You have a real problem with your data model. dates should be stored using the appropriate date/time type, not as strings.

You can convert to a date for the join, using:

select to_date(t1.date, 'YYYY-MM-DD'), t1.col1, t2.col2
from table1 t1 join
     table2 t2
     on to_date(t1.date, 'YYYY-MM-DD') = to_date(t2.date, 'DD.MM.YYYY')