Search code examples
sqloracledate-arithmetic

Date difference = 0 in where clause Oracle?

I have the same problem as this. The only difference is that I use Oracle. I want to select the rows which has insertion_date='20.11.2018'. So my query was 

select * from table where insertion_date='20.11.2018'

In that question they suggested datediff, so I looked at its equivalent in oracle and I learned that I can do date arithmetic. So I tried somethings like these:

select * from table where insertion_date -'20.11.2018'=0;

It gave ora-00932 inconsistent datatypes expected date got number.

So, then I tried;

select * from table where insertion_date - to_date('20.11.2018', 'dd.MM.YYYY') = 0;

It does not give error but also does not display the results which I know there must be. What am I doing wrong here? Thanks.

Update: Sorry I forgot to mention that insertion_date is type date. But it also has time(hour, minutes, seconds) info in it.

Solution

  • What is INSERTION_DATE's datatype?

    If it is DATE, then comparing it to another date (note: this is date literal; value you used is a string!)

    select * from table where insertion_date = date '2018-11-20'

    might work, unless INSERTION_DATE contains time component (hours and minutes). Then, the simplest option is to truncate its value (so that you get date itself, at midnight):

    select * from table where trunc(insertion_date) = date '2018-11-20'

    but it'll ruin index you have on that column (unless it is a function-based one). For small tables, it won't make any difference. For large amount of data, it would so convert it to

    select * from table where insertion_date >= date '2018-11-20'
                      and insertion_date <  date '2018-11-21'

    If, on the other hand, INSERTION_DATE is a string (VARCHAR2 or CHAR) datatype (which is a really bad idea; consider switching to DATE datatype), then you have to know its format, convert it to DATE first and then compare to another date. For example, if it was a string that contains date values in format dd.mm.yyyy, then

    select * from table where to_date(insertion_date, 'dd.mm.yyyy') = date '2018-11-20'

    This will certainly fail if any string in that column doesn't match such a format or contains invalid values (such as "date" 53.67.Bx48).