Getting not valid month in Oracle sql

I have a table called Transactions that has a column called trans_date. I am just trying to do a simple query in the SQL*Plus command window

The query is

SELECT * FROM transactions WHERE 
trans_date BETWEEN to_date('09/11/2021','mm/dd/yyyy') AND to_date('09/12/2021','mm/dd/yyyy');

When I run this query I get not valid month and there is a little * under trans_date. Most of what I have read suggests the query is right but I am not sure what the problem is. The data type is varchar2(20).

Answer

Since trans_date is a varchar and you’re trying to query whether it’s between two dates, you need to convert it to a date too. Assuming it has the same format as the literals in your query:

SELECT * 
FROM   transactions 
WHERE  to_date(trans_date, 'mm/dd/yyy') BETWEEN 
       to_date('09/11/2021','mm/dd/yyyy') AND to_date('09/12/2021','mm/dd/yyyy');