How do i search for a date in a query when sysdate has been used

I have a join query that looks at 2 tables and brings in the dates between a timeframe. I have used sysdate when inserting the data. When I select the data from the table, I am able to see the dates, however, I am not sure how to filter the data by date.

I have tried at the end of my query, but I receive no rows selected:

where dateofstart = '04-MAY-20';

I have also tried:

 where dateofstart = 'date '2020-05-010';

And finally, I have tried:

 dateofstart between '10-May-20' and '16-May-20';

I am using SQL Plus.

Solution

If you used SYSDATE as is, it is a function that contains date and time, e.g.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
06.05.2020 07:20:38

SQL>

and is stored as such into your table (I presume that column's datatype is DATE).

Therefore, if you searched for where dateofstart = '04-MAY-20', you made two mistakes:

you are comparing DATE datatype column with a string. Yes, '04-MAY-20' is a string; it looks like date to you and me, but to Oracle it is just a string. Oracle tries to implicitly convert it to date value using NLS settings; sometimes it succeeds, sometimes not. Therefore, you should always use dates and not rely on possible conversion success.
- imagine you compared dateofstart to '01/02/03'; what is 01? Is it day (could be)? Month (could be as well)? Year (sure, why not)? But, if you used
  - date literal, which is always in format yyyy-mm-dd, e.g. date '2001-03-02', everyone (Oracle included) would know that it is 2nd of March 2001
  - TO_DATE function, e.g. to_date('01/02/03', 'yy/dd/mm')
as your column contains date and time, you'd get some rows only if dateofstart value was stored exactly at midnight at the beginning of that day. If not, either
- "remove" time component, e.g. where trunc(dateofstart) = date '2020-05-04' (which would make use of index (if it existed) on the dateofstart column unusable, unless you create a function-based index), or
- use between, e.g.
```
where dateofstart between to_date('04.05.2020 00:00:00', 'dd.mm.yyyy hh24:mi:ss') 
                      and to_date('04.05.2020 23:59:59', 'dd.mm.yyyy hh24:mi:ss')
```

Your second attempt is wrong (superfluous single quote, 010 (what's that?)).

The third might work if Oracle recognized format you used.

If the above doesn't help, please, post test case (create table and insert into several sample records) (edit the original message you wrote) so that we could see what you did.