How to join 2 tables on fields which have different formats?-CodePudding

I have 2 tables with the following structure:

Table A:
id  - number
a_d - text

where A.a_d has the text format: "yyyy-mm-dd 00:00:00" (examples: 2001-08-22 00:00:00, or 2002-03-23 00:00:00)

Table B:
id  - number
a_d - text

where B.a_d has the text format: "dd-month-yyyy" (example: 01-jul-2002 or 09-feb-2005)

I want to run join query on the text fields of those table.

select a.a_d
from A a
join B b
on a.a_d =?=  b.a_d

I can't change or update the tables, just get data from them

How can I compare this 2 fields, if there have different format ?

CodePudding user response：

Use TO_DATE to convert the text dates into bona fide dates before comparing:

SELECT a.a_d
FROM A a
INNER JOIN B b
    ON a.a_d::date = TO_DATE(b.a_d, 'DD-mon-YYYY');

Note that the a_d field in table A happens to be a text timestamp which can already be directly cast to date, so we only need TO_DATE for the B table.

Ideally you should store your dates and timestamps in proper columns rather than text. Then, the join would be possible without costly conversions.