1

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.

Annon
  • 123
  • 1
  • 9
  • Does this help you? https://stackoverflow.com/questions/23398632/check-if-current-date-is-between-two-dates-oracle-sql – kmoser May 06 '20 at 05:05
  • You have several good answers, but let me throw out one side observation. In your examples above, you sometimes use 2-digit years,and sometimes 4-digit years. Having spent 2 years busting my butt to help fix Y2K ..... please, please, please _always_ use 4-digit years. No exceptions! – EdStevens May 06 '20 at 13:04
  • Thats good info, thanks a lot – Annon May 06 '20 at 22:20

3 Answers3

3

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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
1

Since you cannot match exactly the time you inserted, then you should truncate the date:

where trunc(dateofstart) = '04-MAY-20';
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
1

I would strongly advise you NOT to use between with dates, particularly in Oracle where they have time components. The simplest method that is index-safe is:

where dateofstart >= date '2020-05-10' and
      dateofstart < date '2020-06-16' + interval '1' day

This:

  • is readable
  • uses standard date literals
  • does not require adding a time component to the comparison dates
  • works with or without a time component on dateofstart
  • is index-safe.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786