sql - compare two dates in Postgressql

Question

I have two table

job1

id(c-v) | joiningDate(character-varying)
--+--
1 | 2020-10-05
2 | 2023-10-05
3 | 2021-01-01

job2

id(c-v) | joiningDate(character-varying)
--+--
1 | 2020-10-05
2 | 2023-10-05

Here is sql query

SELECT max(j1."joiningDate")
    FROM public.job1 j1 left join public.job2 j2 ON j1.id=j2.id and 
    j1."joiningDate"  <= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')

I'm trying to fetch the maximum date which is less than current date.. for the I'm getting the empty data.. what would be the issue?

question from:https://stackoverflow.com/questions/65868744/compare-two-dates-in-postgressql

Answer 1

I view the purpose of the join as being a filter on only jobs which match in both tables. If so, then what you really want here is an inner join, and the check on the date being the current date or earlier should be moved to the where clause. Putting this together, perhaps this is what you need here:

SELECT MAX(j1.joiningDate)
FROM public.job1 j1
INNER JOIN public.job2 j2
    ON j1.id = j2.id
WHERE
    j1.joiningDate::date <= CURRENT_DATE;

Note: Consider making the joiningDate column an actual date column in both tables.