SQL 将日期与 oracle 中的 sysdate 进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9147393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Comparing date with sysdate in oracle
提问by TopCoder
I have a column which is of "DATE" type and I want to run a query on it comparing it with sysdate.
我有一个“ DATE”类型的列，我想对它运行一个查询，将它与 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
回答by A.B.Cade
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
您不应该在日期上使用 to_date，To_date 用于将 varchar 转换为日期，而不是日期。
如果您确实在日期上使用了 to_date 函数，那么 oracle 会根据 nls_date_format 将其称为字符串，这在不同环境中可能会有所不同。
正如@jonearles 所说，如果您想删除 sysdate 中的时间，请使用 TRUNC
回答by Jassneet Singh Anand
USE:
用：
select distinct file_name as r
from table_1
where view_day >= TRUNC(SYSDATE-10)
回答by adatapost
Error shows that a VIEW_DAYcolumn is varchar so you need to convert DATE to String. Use TO_CHARor convert VIEW_DAYto date type.
错误显示VIEW_DAY列是 varchar，因此您需要将 DATE 转换为 String。使用TO_CHAR或转换VIEW_DAY为日期类型。
