CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
TopCoder
TopCoder

Reputation: 4296

Comparing date with sysdate in oracle

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

Upvotes: 8

Views: 100226

Answers (3)

Jassneet Singh Anand
Jassneet Singh Anand

Reputation: 61

USE:

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

Upvotes: 6

A.B.Cade
A.B.Cade

Reputation: 16905

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

Upvotes: 18

KV Prajapati
KV Prajapati

Reputation: 94645

Error shows that a VIEW_DAY column is varchar so you need to convert DATE to String. Use TO_CHAR or convert VIEW_DAY to date type.

Upvotes: 0

Related Questions