Date comparison in Oracle database

Question

I have two variable TO_DATE and FROM_DATE. My table has two fields DOJ and DOL. I want to select all the records whose DOJ < TO_DATE and DOL > FROM_DATE.

How can i write an SQL query in for this??

Answer 1

It is not clear what you would like to do.

FIRST INTERPRETATION

I want to select all the records whose DOJ < TO_DATE and, IN THE MEANTIME, DOL > FROM_DATE.

SELECT *
  FROM MY_TABLE
 WHERE DOJ < "TO_DATE" AND DOL > "FROM_DATE"; -- using double quotes in order to
                                              -- avoid confusion with TO_DATE
                                              -- built-in function with the same name.
                                              -- Thanks to kicsit ;-)

SECOND INTERPRETATION

I want to select all the records whose DOJ < TO_DATE and, SEPARATELY, DOL > FROM_DATE.

SELECT *
  FROM MY_TABLE
 WHERE DOJ < "TO_DATE"
UNION -- UNION ALL substituted with UNION, thanks to kicsit user ;-)
SELECT *
  FROM MY_TABLE
 WHERE DOL > "FROM_DATE";

... or - that is the same:

SELECT * 
  FROM MY_TABLE 
 WHERE DOJ < "TO_DATE" OR DOL > "FROM_DATE";

Answer 2

You might run into troubles with the variable named to_date, since that is a built-in Oracle command. You can work around that using double quotes wherever the variable is used, like this:

SELECT * FROM my_table WHERE doj < "to_date" AND dol > from_date;