CODEWITHSUNDEEP
sqlpostgresql
manu
manu

Reputation: 169

compare two dates in Postgressql

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?

Upvotes: 1

Views: 1333

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

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.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

The query you are showing should not result in NULL, but in 2023-10-05 (the maximum date in the table). This is what you have:

SELECT max(j1.joiningDate)
FROM public.job1 j1
LEFT OUTER JOIN public.job2 j2 ON <conditions>

You see that the table job2 has no impact on the result (and nothing to do with the task at hand as far as I can see).

You want a WHERE clause to restrict the rows to dates before today:

WHERE j1.joiningDate <= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')

The whole query:

SELECT max(j1.joiningDate)
FROM public.job1 j1
WHERE j1.joiningDate <= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD');

If you are really getting null (or no result) with your original query, then this is not a problem with the query. You should get a result as mentioned (albeit not the desired result at that). It must be your tool or app. Or you are not showing the complete query. Or there is no data in table job1.

Upvotes: 1

Related Questions