CODEWITHSUNDEEP
sqloracle-databasemybatis
stack man
stack man

Reputation: 2413

SQL checking if value is between period

I am trying to check if the date is between start_date and end_date, but this won't work: 

SELECT * FROM table_foo
WHERE start_date BEFORE '2015-02-21T00:00:00' AND 
      end_date AFTER '2015-02-21T00:00:00';

What am I missing?

Thanks

UPDATE:

Can I use NOW() instead of '2015-02-21T00:00:00'?

Upvotes: 0

Views: 304

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

Almost...

SELECT * FROM table_foo
WHERE start_date < TIMESTAMP '2015-02-21 00:00:00.0' AND 
      end_date > TIMESTAMP '2015-02-21 00:00:00.0';

This makes use of the SQL standard TIMESTAMP literal syntax, which consists of the keyword TIMESTAMP and an ISO 8601 date time representation (without the T!). You can then easily use < and > to compare timestamps.

From your comments, if you want to use the current time, write:

SELECT * FROM table_foo
WHERE start_date < SYSTIMESTAMP AND 
      end_date > SYSTIMESTAMP;

Or even:

SELECT * FROM table_foo
WHERE SYSTIMESTAMP BETWEEN start_date AND end_date;

Upvotes: 9

Related Questions