sql query to get data between two dates

Question

I have a table comp_sal-

  PERSON_NUMBER         DATE_FROM                 DATE_TO                   DATE_COMP              SALARY_AMOUNT
    12                      01-06-2019              25-09-2021                  22-02-2020              24.38
    12                      16-07-2018              31-05-2019                  22-02-2020              23.5

I have created a query to get the salary for 2 years back from a table,comp_sal -

select * from 
(
select person_number,
salary_amount,
 to_char(date_from,'dd-mm-yyyy') date_From ,
to_char(date_to,'dd-mm-yyyy') date_to,
 ((Select to_char(sysdate,'dd-mm-')||To_char(Add_months(SYSDATE, -24), 'yyyy') from dual)) date_comp
 
 from comp_sal
 )
  where Date_comp between DATE_fROM and date_to

But I am getting the both rows as above and not just the first row because 22-02-2020 is between 01-06-2019 and 25-09-2021. I am also getting 16-07-2018 and 31-05-2019 row in the output even after adding Date_comp between DATE_fROM and date_to condition

Answer 1

Even if you have (correctly) defined your columns as dates, your query will get that result because you are using a subquery that converts them to strings and then compares those.

If you leave everything as dates int he subquery and convert to strings only at the last moment in the outer query, for display only:

select person_number,
  to_char(date_from, 'dd-mm-yyyy') as date_from,
  to_char(date_to, 'dd-mm-yyyy') as date_to,
  to_char(date_comp, 'dd-mm-yyyy') as date_comp,
  salary_amount
from (
  select person_number,
    date_from,
    date_to,
    add_months(trunc(sysdate), -24) as date_comp,
    salary_amount
  from comp_sal
)
where date_comp between date_from and date_to

... then it would only find the single row you expect.

You could also avoid the subquery against your real table, and instead get the date_comp from a separate query that's cross-joined/applied to the real table:

select person_number,
  to_char(cs.date_from, 'dd-mm-yyyy') as date_from,
  to_char(cs.date_to, 'dd-mm-yyyy') as date_to,
  to_char(t.date_comp, 'dd-mm-yyyy') as date_comp,
  salary_amount
from comp_sal cs
cross apply (select add_months(trunc(sysdate), -24) as date_comp from dual) t
where t.date_comp between cs.date_from and cs.date_to

Either way, with your sample data as dates you get the same result:

PERSON_NUMBER	DATE_FROM	DATE_TO	DATE_COMP	SALARY_AMOUNT
12	01-06-2019	25-09-2021	22-02-2020	24.38

db<>fiddle