Use AND and OR within where clause for dates

I need to filter a collection of records that satisfies a single date OR satisfies a range of dates. How is this done in conjunction? For example, I need to filter records that has the date 02-FEB-24 OR within the range of 08-FEB-25 to 20-FEB-25. This is what I have:

SELECT userId, COUNT(*), TO_DATE(trans_dt, 'DD-MON-RR') 
FROM msgstore
where trans_dt BETWEEN TO_DATE('08-FEB-25', 'DD-MON-RR') AND TO_DATE('20-FEB-25', 'DD-MON-RR')
OR trans_dt = TO_DATE('02-FEB-24', 'DD-MON-RR')
AND SEND_ID = 1020
GROUP BY userId, TO_DATE(trans_dt, 'DD-MON-RR') 

I am getting this error message: unable to convert string value containing %s to a number: %s

If I remove one of the trans_dt conditionals in the query, it works. It fails with the error when both trans_dt conditionals are present.

Assuming that the trans_dt column is a DATE data-type.

1. Do not use TO_DATE on a value that is already a DATE (i.e. in the SELECT and GROUP BY clauses) - that is probably the cause of your error.
2. AND has higher operator precedence than OR; to get the correct logic in the WHERE filter you need to add brackets to change the precedence.
3. In Oracle, a DATE data-type consists of 7 bytes representing: century, year-of-century, month, day, hours, minutes and seconds. It ALWAYS has those components (even if the client you are using is setup to only show the date component and not the time component); so you probably (unless you are only storing mignight time components) want to filter on entire 24-hour periods and TRUNCate the date when grouping.

Like this:

SELECT userId,
       COUNT(*),
       TRUNC(trans_dt) AS trans_dt
FROM   msgstore
WHERE  (  (trans_dt >= DATE '2025-02-08' AND trans_dt < DATE '2025-02-21')
       OR (trans_dt >= DATE '2024-02-02' AND trans_dt < DATE '2024-02-03')
       )
AND    SEND_ID = 1020
GROUP BY
       userId,
       TRUNC(trans_dt)

Which, for the sample data:

CREATE TABLE msgstore (userid, trans_dt, send_id) AS
SELECT 1, DATE '2024-02-01' + LEVEL * INTERVAL '1' HOUR, 1020 FROM DUAL CONNECT BY LEVEL <= 50 UNION ALL
SELECT 1, DATE '2025-02-08' + LEVEL * INTERVAL '1' HOUR, 1020 FROM DUAL CONNECT BY LEVEL <= 144 UNION ALL
SELECT 2, DATE '2025-02-12' + LEVEL * INTERVAL '1' HOUR, 1020 FROM DUAL CONNECT BY LEVEL <= 50;

244 rows affected

Outputs:

fiddle