I have a query that is not returning the last date informed, even though I put a smaller or equal operator. If I put the Start date greater than, it returns, but if I put less or equal, it does not return.
What would be the reason?
Period: 26-08-2021 to 25-09-2021
WHERE B.PLOP_DT_FECHAMENTO >= TO_DATE(:FRISTDATE, 'DDMMYYYY')
AND B.PLOP_DT_FECHAMENTO <= TO_DATE(:LASTDATE, 'DDMMYYYY')
Using Only Start date
Using Start and End dates
What would be the reason?
TO_DATE(:LASTDATE, 'DDMMYYYY')
Will set the time component to the default value of midnight, so your query is looking for the bounds: 2021-08-26T00:00:00 to 2021-09-25T00:00:00. Since, 2021-09-25T:05:04:50 is outside of that range it is excluded.
If you want the entire day then you have to specify a range that includes all the times of the day.
You could add a time component:
WHERE B.PLOP_DT_FECHAMENTO >= TO_DATE(:FIRSTDATE || '000000', 'DDMMYYYYHH24MISS')
AND B.PLOP_DT_FECHAMENTO <= TO_DATE(:LASTDATE || '235959', 'DDMMYYYYHH24MISS')
Or you could add a day and use the <operator:
WHERE B.PLOP_DT_FECHAMENTO >= TO_DATE(:FIRSTDATE, 'DDMMYYYY')
AND B.PLOP_DT_FECHAMENTO < TO_DATE(:LASTDATE, 'DDMMYYYY') + INTERVAL '1' DAY
As explained in the comment the problem is caused by the missing time component of the upper bound date.
The solution is to change the second predicate from
B.PLOP_DT_FECHAMENTO <= TO_DATE(:LASTDATE, 'DDMMYYYY')
to (add one day and change the predicate to <)
B.PLOP_DT_FECHAMENTO < TO_DATE(:LASTDATE, 'DDMMYYYY') + 1