SQL 查找前 x 天的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6782999/
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
find records from previous x days?
提问by dexter
How can I come up with a stored procedure that selects results for past 30 days?
我怎样才能想出一个选择过去 30 天的结果的存储过程？
where MONTH(RequestDate) > 6 and DAY(RequestDate) >= 10
and MONTH(RequestDate) < 21 and DAY(RequestDate) < 7
回答by Neil Knight
SELECT *
FROM Table
WHERE GETDATE() >= DATEADD(DAY, -30, GETDATE())
Substitute the first GETDATE() with the appropriate column name.
用适当的列名替换第一个 GETDATE()。
SELECT *
FROM Table
WHERE Table.ColumnName >= DATEADD(DAY, -30, GETDATE())
回答by Nathan Skerl
Are you looking for last 30 days or last month? To find start and end of each month ("generic" as your comment says), use:
您是在寻找过去 30 天还是上个月？要查找每个月的开始和结束（如您的评论所说的“通用”），请使用：
select dateadd(month,datediff(month,0,getdate()),0),
dateadd(mm,datediff(mm,-1,getdate()),-1)
回答by p.campbell
Something like this?
像这样的东西？
CREATE PROC GetSomeHistory
@NumDaysPrevious int
AS
BEGIN
SELECT * FROM MyTable
WHERE RequestDate BETWEEN DATEADD(dd, -1 * @NumDaysPrevious, getdate())
AND getdate();
END
......
EXEC GetSomeHistory 55;
回答by Chains
SELECT *
FROM Table
WHERE myDate >= DATEADD(MONTH, -1, GETDATE())
doing it by month is different than doing it in 30-day blocks. Test this out as follows...
按月做和按 30 天做是不同的。测试如下...
declare @mydate smalldatetime
set @mydate = '07/6/01'
select @mydate
select DATEADD(month, 2, @mydate), DATEDIFF(day, DATEADD(month, 2, @mydate), @mydate)
select DATEADD(month, 1, @mydate), DATEDIFF(day, DATEADD(month, 1, @mydate), @mydate)
select DATEADD(month, -1, @mydate), DATEDIFF(day, DATEADD(month, -1, @mydate), @mydate)
select DATEADD(month, -2, @mydate), DATEDIFF(day, DATEADD(month, -2, @mydate), @mydate)
select DATEADD(month, -3, @mydate), DATEDIFF(day, DATEADD(month, -3, @mydate), @mydate)
Here are the results:
结果如下：
2001-07-06 00:00:00
2001-09-06 00:00:00 | -62
2001-08-06 00:00:00 | -31
2001-06-06 00:00:00 | 30
2001-06-06 00:00:00 | 30
2001-05-06 00:00:00 | 61
2001-04-06 00:00:00 | 91
