针对日期列的 Oracle SQL Where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4091132/
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
Oracle SQL Where Clause against a date column
提问by anwarma
I have a DATE column with a date in it but I need to query it to find records less than 30 days old.
我有一个带有日期的 DATE 列，但我需要查询它以查找不到 30 天的记录。
START_DATE
----------
01-AUG-2010
09-AUG-2010
22-AUG-2010
09-SEP-2010
Query:
询问：
SELECT START_DATE
WHERE START_DATE < 30;
I know it is simple Query in ORACLE SQL but i am doing something wrong.
我知道这是 ORACLE SQL 中的简单查询，但我做错了什么。
采纳答案by maximdim
WHERE START_DATE > SYSDATE - 1
WHERE START_DATE > SYSDATE - 1
or perhaps
也许
WHERE TRIM(STARTDATE) > TRIM(SYSDATE) - 1
WHERE TRIM(STARTDATE) > TRIM(SYSDATE) - 1
回答by OMG Ponies
Use:
用：
SELECT t.start_date
FROM YOUR_TABLE t
WHERE t.start_date > SYSDATE - 30
SYSDATEis Oracle's syntax to get the current date and time- In Oracle, you can do date arithmetic in the context of days, so SYSDATE - 30 means "current date, subtract thirty days" to get a date that is thirty days in the past
SYSDATE是 Oracle 获取当前日期和时间的语法- 在 Oracle 中，您可以在天的上下文中进行日期算术，因此 SYSDATE - 30 表示“当前日期，减去三十天”以获得过去三十天的日期
If you want to evaluate the date based on thirty days as of midnight, use the TRUNC function:
如果要根据午夜的三十天计算日期，请使用TRUNC 函数：
SELECT t.start_date
FROM YOUR_TABLE t
WHERE t.start_date > TRUNC(SYSDATE) - 30
Don't run TRUNC on the column - that will render an index on the column useless, ensuring a table scan.
不要在列上运行 TRUNC - 这将使列上的索引无用，从而确保表扫描。
回答by Paul Tomblin
SELECT t.start_date
FROM YOUR_TABLE t
WHERE t.start_date > SYSDATE - INTERVAL '30' DAY;
INTERVALis more portable than assuming that you can add or subtract days, although I've noticed some slight differences in the INTERVALsyntax between Oracle and PostgreSQL.
INTERVAL尽管我注意到INTERVALOracle 和 PostgreSQL 之间的语法存在一些细微差别，但它比假设您可以添加或减去天数更具可移植性。
