Hello,
I need to have all the complete records from Nov 1, 2023 until Nov 17, 2023 but I do not have all the records.
AND rcv_date BETWEEN TRUNC(TO_DATE('01/11/23', 'DD/MM/YY')) AND TRUNC(TO_DATE('17/11/23', 'DD/MM/YY'))
AND rcv_date BETWEEN TO_DATE('01/11/23', 'DD/MM/YY') AND TRUNC(TO_DATE('18/11/23', 'DD/MM/YY')) - 1
the following sql statement is possible to have all the records
AND sh.rcv_date BETWEEN TO_DATE('31/10/23', 'DD/MM/YY') AND TO_DATE('18/11/23', 'DD/MM/YY')
Could you help me improve the sql script?
thank you so much
Might want to pose your question to a more appropriate forum that deals with SQL semantics...
That said, the last WHERE clause phrase looks like the one to give you the desired record set.
Not sure what the ask here is (e.g. to "improve" the SQL?)
I never use between because I personally just don't like the way it reads. I don't think any of the above is correct.
This one misses everything after 12:00:01 on the 23rd. For what it's worth, there is no point to TRUNC here because the date value you've supplied does not have a time component.
AND rcv_date BETWEEN TRUNC(TO_DATE('01/11/23', 'DD/MM/YY')) AND TRUNC(TO_DATE('17/11/23', 'DD/MM/YY'))
This one is the same as before, just a more complicated way to express Nov 17th.
AND rcv_date BETWEEN TO_DATE('01/11/23', 'DD/MM/YY') AND TRUNC(TO_DATE('18/11/23', 'DD/MM/YY')) - 1
This one will give you everything on Oct 31, and also Nov 18th at 12:00:00 which you don't want.
AND sh.rcv_date BETWEEN TO_DATE('31/10/23', 'DD/MM/YY') AND TO_DATE('18/11/23', 'DD/MM/YY')
I personally would write it as the following. It is simple and unambiguous.
AND sh.rcv_date >= TO_DATE('01/11/2023 00:00:00', 'DD/MM/YYYY, hh24:mi:ss')
AND sh.rcv_date <= TO_DATE('17/11/2023 23:59:59', 'DD/MM/YYYY, hh24:mi:ss')
BETWEEN is kind of ambiguous. It feels like exclusive but yet begin and end values are included! Is 1 between 1 and 2 ? Yes it is. 
I understand John completely. 
1 Like
That's exactly how I feel about it, Andre.
And thank you guys, Andre and John, for responding to questions that don't relate to Toad usability.
What a great forum!
Hello.
What is the most appropriate forum that deals with SQL semantics.
Please excuse me.
Could you write me the URL, I promise to improve this error.
And thank you very much for the response and correcting my sql statement
There's quite a few, but I've had good experiences with these: