Oracle - Date substraction in where clause

Question

I'm trying to figure out how to compare the result of a date substraction in a where clause.

Clients subscribed to a service and therefore are linked to a subscription that has an end date. I want to display the list of subscriptions that will come to an end within the next 2 weeks. I did not designed the databse but noticed that the End_Date column type is a varchar and not a date.. I can't change that.

My problem is the following: If I try to select the result of the substraction for example with this request:

SELECT(TO_DATE(s.end_date,'YYYY-MM-DD') - TRUNC(SYSDATE)) , s.name  
from SUBSCRIPTION s WHERE s.id_acces = 15 

This will work and give me the number of days between the end of the subscription and the current date.

BUT now, if I try to include the exact same request in a clause where for comparison:

SELECT s.name 
from SUBSCRIPTION S 
WHERE (TO_DATE(s.end_date,'YYYY-MM-DD') - TRUNC(SYSDATE))  between 0 and 16

I will get an error: "ORA-01839 : date not valid for month specified". Any help would be appreciated..

Answer 1

" the End_Date column type is a varchar and not a date.. I can't change that."

If you can't change the date you'll have to chang3 the data. You can identify the rogue values with this function:

create or replace check_date_format (p_str in varchar2) return varchar2
is
   d date;
begin
   d := to_date(p_str,'YYYY-MM-DD');
   return 'VALID';
exception
   when others then
     return 'INVALID';
end;

You can use this function in a query:

select sid, end_date
from SUBSCRIPTION 
where check_date_format(end_date) != 'VALID';

Your choices are:

1. fix the data so all the dates are in the same format
2. fix the data and apply a check constraint to enforce future validity
3. write a bespoke MY_TO_DATE() function which takes a string and applies lots of different date format masks to it in the hope of getting a successful conversion.