Getting data between given date range by comparing two dates in a table-CodePudding

I have a table trans and it has columns like tranno, start_date, stop_date. Data is as follows.

tranno, start_date, stop_date
123, 07-jul-2019, 06-jun-2020
124, 07-jul-2020, 06-jun-2021
125, 07-jul-2021, 06-jun-2022
126, 07-jul-2022, 06-jun-2023

My requirement is , I want to get trans between given date range. Lets say i want trans between 08-jul-2020 and 10-aug-2022. Then my output should be like

Tranno
124
125
126

CodePudding user response：

Assuming your start_date and stop_date columns are bona fide date columns, then you may use the overlapping date range formula here:

SELECT *
FROM yourTable
WHERE stop_date  >= '2020-07-08'::date AND
      start_date <= '2022-08-10'::date;

If you are literally storing text dates as e.g. 07-jul-2019, then the best thing to do would be to change your table design and store proper dates.