使用 SQL 从日期中减去 n 天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7336354/
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
Subtracting n Days from a date using SQL
提问by MrMokari
I am quite a beginner when it comes to Oracle. I am having trouble figuring out how to do something similar to this :
说到 Oracle，我是一个初学者。我无法弄清楚如何做类似的事情：
SELECT ID, NAME, TO_CHAR(DATEBIRTH, 'DD/MM/YYYY HH24:MI:SS')
FROM PEOPLE WHERE DATEBIRTH >= ANOTHERDATE - NDAY
To put it short, I want to select everyone who were born N days before a specific date and time but I am not quite sure that this is the way to do it nor that it would give me the results I expect.
简而言之，我想选择在特定日期和时间之前 N 天出生的每个人，但我不太确定这是这样做的方式，也不确定它会给我期望的结果。
PS: I am developping under oracle8i.
PS：我是在oracle8i下开发的。
采纳答案by Lukas Eder
Your query looks correct to me. That's how you subtract days from dates in Oracle. This link holds some more insight for you, should you want to add months or years:
您的查询在我看来是正确的。这就是在 Oracle 中从日期中减去天数的方法。如果您想添加月份或年份，此链接可为您提供更多见解：
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1157035034361
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1157035034361
回答by Ollie
You might want to consider the time portion of your date "ANOTHERDATE".
您可能需要考虑日期“ANOTHERDATE”的时间部分。
If you are only concerned with whole days then you could rewrite your query as:
如果您只关心一整天，那么您可以将查询重写为：
SELECT ID, NAME, TO_CHAR(DATEBIRTH, 'DD/MM/YYYY HH24:MI:SS')
FROM PEOPLE
WHERE DATEBIRTH >= TRUNC(ANOTHERDATE - NDAY)
N.B. This is assuming "NDAY" is a numeric.
注意这是假设“NDAY”是一个数字。
"To put it short, I want to select everyone who were born N days before a specific date and time but I am not quite sure that this is the way to do it nor that it would give me the results I expect."
“简而言之，我想选择在特定日期和时间前 N 天出生的每个人，但我不太确定这是这样做的方式，也不确定它会给我预期的结果。”
My first query would get you everyone with a birthday ON OR AFTER"NDAY" days before "ANOTHERDATE".
我的第一个查询会在“ANOTHERDATE”之前的“NDAY”天或之后为每个人提供生日。
This will get you everyone who has a birthday ONthe day that is "NDAY" days before "ANOTHERDATE":
这将让你大家谁过生日开的那一天是“ANOTHERDATE”之前“NDAY”天：
SELECT id,
name,
TO_CHAR(datebirth, 'DD/MM/YYYY HH24:MI:SS') AS birth_date
FROM people
WHERE TRUNC(datebirth) = TRUNC(anotherdate - NDAY);
If there is an index on the "datebirth" column then you do not want to wrap it with TRUNC so you could use the following which would be able to use any index on "datebirth":
如果“datebirth”列上有一个索引，那么您不想用 TRUNC 包装它，因此您可以使用以下可以使用“datebirth”的任何索引：
SELECT id,
name,
TO_CHAR(datebirth, 'DD/MM/YYYY HH24:MI:SS') AS birth_date
FROM people
WHERE datebirth >= TRUNC(anotherdate - NDAY)
AND datebirth < (TRUNC(anotherdate - NDAY) + 1);
回答by Bashar
You have to convert (anotherdate) explicitly to date then subtract (ndays) from it:
您必须将（另一个日期）显式转换为日期，然后从中减去（ndays）：
SELECT ID, NAME, TO_CHAR(DATEBIRTH, 'DD/MM/YYYY HH24:MI:SS')
FROM PEOPLE
WHERE DATEBIRTH >= TO_DATE(ANOTHERDATE) - NDAY
回答by user2817788
Below query subtract n days from given date
下面的查询从给定日期减去 n 天
select to_date('date') - 'n days'
from dual
example:
例子：
select TO_DATE('02-Jul-16') - 90
from dual
