Search code examples
javasqlpostgresqldatepostgresql-13

How to retrieve the rows between two dates in PostgreSQL?

How to retrieve the row between two dates in PostgreSQL?

ID        START_DATE        END_DATE

1         02/03/2020       02/03/2021

2         05/04/2020       NULL

In the above example, end_date column is NULLABLE.

I want to retrieve the row using the date which falls in-between START_DATE & END_DATE. Endate NULL is considered it as infinite.

I am clueless on how to pass single date input to two columns and handle NULL

Solution

  • If you are using Postgres, you can use infinity for the end value. I would suggest using that instead of NULL. This is explained in the documentation.

    In the meantime, you can do:

    date >= start_date and
(date < end_date or end_date is null)

    I'm not sure if end_date is inclusive or not, so this might be <=.