ORA-00932 convert and format dates and strings to calculate a range of days-CodePudding

I have a table where the date_one field is of type VARCHAR2 (20 BYTE) and the date_two field is of type DATE.

Output for date_one is 29-01-2019 20:22:08 and output for date_two is 25-JAN-19.

I need to calculate records based on an interval of days between these two fields but I'm having a hard time with this error:

select field1,field2,field3 
from mytable
where 
trunc(to_date(DATE_ONE, 'DD-MM-YYYY HH24:MI:SS')) - trunc(to_date(DATE_TWO 'DD-MM-YYYY')) > interval '15' day;

ORA-00932: inconsistent datatypes: expected NUMBER, got INTERVAL DAY TO SECOND 00932. 00000 - "inconsistent datatypes: expected %s got %s"

How can I fix this error?

CodePudding user response：

The result of subtracting two dates is a number of days, unlike the result of subtracting two timestamps, which is an interval. You are currently trying to compare the number you get from that subtraction with a fixed interval - which is what the error is saying. The data types don't match and can't be implicitly converted for comparison.

You can either convert that number to an interval:

select field1,field2,field3
from mytable
where (trunc(to_date(DATE_ONE, 'DD-MM-YYYY HH24:MI:SS')) - trunc(DATE_TWO)) * interval '1' day > interval '15' day;

or more simply just compare with the number 15:

select field1,field2,field3
from mytable
where trunc(to_date(DATE_ONE, 'DD-MM-YYYY HH24:MI:SS')) - trunc(DATE_TWO) > 15;

db<>fiddle

I've changed trunc(to_date(DATE_TWO, 'DD-MM-YYYY')) to simply trunc(DATE_TWO). Because DATE_TWO is already a date, when you do to_date(DATE_TWO, 'DD-MM-YYYY') you are implicitly converting the date to a string using your session's NLS_DATE_FORMAT setting (which seems to be DD-MON-RR), and then converting that string back to a date. As well as being a waste of effort, that may break if this is run in a session with different settings. You just need to truncate the date you already have.