How to query Oracle tables based on some date ?

You can use any of the below query, provided the data type of the MY_DATE_COLUMN is of Date type:

select * from MY_TABLE where trunc(MY_DATE_COLUMN) =to_date('28-JUL-22','dd-MON-yy');
select * from MY_TABLE where to_date(MY_DATE_COLUMN) ='28-JUL-22';

In the above query the trunc() is used to remove any trailing Timestamp in the data.