Comparing date with sysdate in oracle

sql oracle date-arithmetic
TopCoder picture TopCoder · Feb 5, 2012 · Viewed 92.4k times · Source

I have a column which is of "DATE" type and I want to run a query on it comparing it with sysdate.

But I am getting following error, Can someone please let me know what I am 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

A.B.Cade picture A.B.Cade · Feb 5, 2012

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

Related questions

Oracle DateTime in Where Clause?

I have sql something like this: SELECT EMP_NAME, DEPT FROM EMPLOYEE WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') -> This returns 10 rows and TIME_CREATED = '26-JAN-2011' Now when i do this …

Read More
Oracle date "Between" Query

I am using oracle database. i want to execute one query to check the data between two dates. NAME START_DATE ------------- ------------- Small Widget 15-JAN-10 04.25.32.000000 PM Product 1 17-JAN-10 04.31.32.000000 PM select * from <TABLENAME> where start_date BETWEEN '15…

Read More
Oracle SQL Where clause to find date records older than 30 days

I want to find records in a (Oracle SQL) table using the creation date field where records are older than 30 days. It would be nice to find records using a operators like > but if anyone can suggest quick SQL …

Read More