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

CodePudding user response：

You didn't answer what I asked as a comment, so - here's a demo. I suspect you actually stored dates as strings and get wrong result:

SQL> with comp_sal (person_number, date_from, date_To, date_comp, salary_amount) as
  2    (select '12', '01-06-2019', '25-09-2021', '22-02-2020', 24.38 from dual union all
  3     select '12', '16-07-2018', '31-05-2019', '22-02-2020', 23.5  from dual
  4    )
  5  select *
  6  from comp_sal
  7  where date_comp between date_from and date_to;

PE 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

SQL>

If you convert strings to dates - using the TO_DATE function with appropriate format mask - then you get correct result:

SQL> with comp_sal (person_number, date_from, date_To, date_comp, salary_amount) as
  2    (select '12', '01-06-2019', '25-09-2021', '22-02-2020', 24.38 from dual union all
  3     select '12', '16-07-2018', '31-05-2019', '22-02-2020', 23.5  from dual
  4    )
  5  select *
  6  from comp_sal
  7  where to_date(date_comp, 'dd-mm-yyyy') between to_date(date_from, 'dd-mm-yyyy')
  8                                             and to_date(date_to, 'dd-mm-yyyy');

PE DATE_FROM  DATE_TO    DATE_COMP  SALARY_AMOUNT
-- ---------- ---------- ---------- -------------
12 01-06-2019 25-09-2021 22-02-2020         24,38

SQL>

CodePudding user response：

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:

db<>fiddle