1

I have this query:

SELECT "visits".*
FROM "visits"
where   
    '2015-06-20' between start_date and end_date
or
    if (end_date is null) then end_date = '9999-01-01'

I'm using PostgreSQL 9.4.

The question is:

How to test if a date is in a range also without end_date in some rows?

IF ELSE THEN doesn't work in Postgres also if I use 1=1 or similar...

How to write it?

Improve this question

2 Answers 2

Reset to default
2

Use coalesce():

select *
from visits
where date '2015-06-20' between start_date and coalesce(end_date, date '9999-01-01');

Or to make the "end" more obvious you can use infinity:

select *
from visits
where date '2015-06-20' between start_date and coalesce(end_date, 'infinity');
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

It workas, thanks, but if I want to use "IF", can you help me? Why it throw me an error on then?
IF doesn't exist in SQL, use CASE: postgresql.org/docs/current/interactive/…
Oh, wow! And why this page? postgresql.org/docs/9.4/static/plpgsql-control-structures.html
@JohnSam That is the documentation of the syntax for the procedural language PL/pgSQL which is used to write stored functions. That is not the documentation for SQL. There is no IF in SQL.
0

use COALESCE and OVERLAPS.

SELECT v.*
FROM visits
WHERE (start_date,COALESCE(end_date, '9999-01-01'::date)) OVERLAPS ('2015-06-20'::date,'2015-06-20'::date)
Improve this answer

1 Comment

It workas, thanks, but if I want to use "IF", can you help me? Why it throw me an error on then?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.