Search between two date does not return last date informed-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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