Compare date with sysdate in oracle

Question

I have a column of type " DATE " and I want to run the query by comparing it with sysdate.

But I get the following error, can someone please let me know what I'm missing here?

 SQL> select distinct file_name as r from table_1 where view_day >= TO_DATE(SYSDATE-10, 'YYYY/MM/DD'); ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected 

Answer 1

You should not use to_date in a date, To_date is for casting varchar to date, not date.
If you use the to_date function in a date, then the oracle will refer to it as a string according to nls_date_format, which may differ in different environments.
As @jonearles said, if you want to remove time in sysdate, use TRUNC

Answer 2

Using:

 select distinct file_name as r from table_1 where view_day >= TRUNC(SYSDATE-10) 

Answer 3

The error indicates that the VIEW_DAY column is varchar, so you need to convert the DATE to String. Use TO_CHAR or convert VIEW_DAY to date type.