SQL BETWEEN Statement is returning only match results-CodePudding

I am using SQL Oracle DB, I am facing issue when using SQL Between Statement. I have a requirement to search the values in between the date time range. When I am using the below SQL statement, I am only receiving matching results or empty set but I need the results which are between the values inclusive the passing values if present.

select id,date 
from Table_name 
where date between '2020-10-21 10:00:17' AND '2017-10-21 22:00:17' 
  and id = '123';

I am receiving as Empty set

Example of output should be

ID       DATE
123    2020-10-21 10:00:17
123    2020-09-07 09:87:22
123    2018-06-09 07:58:01
123    2017-08-12 08:00:10

Could anyone suggest what should be modified in the sql statement

CodePudding user response：

Try to swap the two date values after between in your SQL statement.

Explanation: between in this statement means:

date >= '2020-10-21 10:00:17' and date <= '2017-10-21 22:00:17'

The correct statement is:

select id, date
  from Table_name
 where date between '2017-10-21 22:00:17' and '2020-10-21 10:00:17'
   and id = '123';

CodePudding user response：

You have two problems:

You want the lower bound of the range before the upper bound in the BETWEEN clause; and
You are not comparing on a date-time range, you are comparing on an alpha-numeric string comparison. This is because the values in the BETWEEN clause are string literals and not DATE values.

You want to use DATE values in the BETWEEN clause:

select id,
       date_column
from   Table_name
where  date_column BETWEEN DATE '2017-10-21'   INTERVAL '22:00:17' HOUR TO SECOND
                       AND DATE '2020-10-21'   INTERVAL '10:00:17' HOUR TO SECOND
and    id = '123';

select id,
       date_column
from   Table_name
where  date_column BETWEEN TIMESTAMP '2017-10-21 22:00:17'
                       AND TIMESTAMP '2020-10-21 10:00:17'
and    id = '123';

select id,
       date_column
from   Table_name
where  date_column BETWEEN TO_DATE('2017-10-21 22:00:17', 'YYYY-MM-DD HH24:MI:SS')
                       AND TO_DATE('2020-10-21 10:00:17', 'YYYY-MM-DD HH24:MI:SS')
and    id = '123';

If you use string literals and the NLS_DATE_FORMAT session parameter does not match then you will get an error:

select id,
       date_column
from   Table_name
where  date_column between '2017-10-21 22:00:17' and '2020-10-21 10:00:17'
and    id = '123';

Outputs:

ORA-01861: literal does not match format string

db<>fiddle here