SQL Server：仅获取过去一年的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30222/
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
SQL Server: Get data for only the past year
提问by Josh Mein
I am writing a query in which I have to get the data for only the last year. What is the best way to do this?
我正在编写一个查询，我必须在其中获取仅去年的数据。做这个的最好方式是什么？
SELECT ... FROM ... WHERE date > '8/27/2007 12:00:00 AM'
回答by samjudson
The following adds -1 years to the current date:
以下将当前日期增加 -1 年：
SELECT ... From ... WHERE date > DATEADD(year,-1,GETDATE())
回答by D.E. White
I found this page while looking for a solution that would help me select results from a prior calendar year. Most of the results shown above seems return items from the past 365 days, which didn't work for me.
我在寻找可以帮助我选择前一个日历年结果的解决方案时发现了此页面。上面显示的大多数结果似乎都是过去 365 天的退货，这对我不起作用。
At the same time, it did give me enough direction to solve my needs in the following code - which I'm posting here for any others who have the same need as mine and who may come across this page in searching for a solution.
同时，它确实给了我足够的方向来解决我在以下代码中的需求——我将这些代码发布在这里，供与我有相同需求的任何其他人使用，并且可能会在此页面上寻找解决方案。
SELECT .... FROM .... WHERE year(*your date column*) = year(DATEADD(year,-1,getdate()))
Thanks to those above whose solutions helped me arrive at what I needed.
感谢上面的人，他们的解决方案帮助我达到了我所需要的。
回答by Ivan Bosnic
Well, I think something is missing here. User wants to get data from the last year and not from the last 365 days. There is a huge diference. In my opinion, data from the last year is every data from 2007 (if I am in 2008 now). So the right answer would be:
好吧，我认为这里缺少一些东西。用户想要获取去年的数据，而不是过去 365 天的数据。有很大的不同。在我看来，去年的数据是 2007 年的所有数据（如果我现在是 2008 年）。所以正确的答案是：
SELECT ... FROM ... WHERE YEAR(DATE) = YEAR(GETDATE()) - 1
Then if you want to restrict this query, you can add some other filter, but always searching in the last year.
然后如果你想限制这个查询，你可以添加一些其他过滤器，但总是在去年搜索。
SELECT ... FROM ... WHERE YEAR(DATE) = YEAR(GETDATE()) - 1 AND DATE > '05/05/2007'
回答by Mark Brackett
The most readable, IMO:
最易读的，IMO：
SELECT * FROM TABLE WHERE Date >
DATEADD(yy, -1, CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)))
Which:
哪一个：
- Gets now's datetime GETDATE() = #8/27/2008 10:23am#
- Converts to a string with format 101 CONVERT(varchar, #8/27/2008 10:23am#, 101) = '8/27/2007'
- Converts to a datetime CONVERT(datetime, '8/27/2007') = #8/27/2008 12:00AM#
- Subtracts 1 year DATEADD(yy, -1, #8/27/2008 12:00AM#) = #8/27/2007 12:00AM#
- 获取现在的日期时间GETDATE() = #8/27/2008 10:23am#
- 转换为格式为 101 的字符串CONVERT(varchar, #8/27/2008 10:23am#, 101) = '8/27/2007'
- 转换为日期时间CONVERT(datetime, '8/27/2007') = #8/27/2008 12:00AM#
- 减去 1 年DATEADD(yy, -1, #8/27/2008 12:00AM#) = #8/27/2007 12:00AM#
There's variants with DATEDIFF and DATEADD to get you midnight of today, but they tend to be rather obtuse (though slightly better on performance - not that you'd notice compared to the reads required to fetch the data).
有 DATEDIFF 和 DATEADD 的变体可以让你今天午夜，但它们往往相当迟钝（虽然在性能上稍微好一点 - 与获取数据所需的读取相比，你不会注意到）。
回答by SQLMenace
Look up dateadd in BOL
在 BOL 中查找 dateadd
dateadd(yy,-1,getdate())
回答by Grzegorz Gierlik
GETDATE() returns current date and time.
GETDATE() 返回当前日期和时间。
If last yearstarts in midnight of current day last year (like in original example) you should use something like:
如果去年从去年当天的午夜开始（就像在原始示例中一样），您应该使用以下内容：
DECLARE @start datetime
SET @start = dbo.getdatewithouttime(DATEADD(year, -1, GETDATE())) -- cut time (hours, minutes, ect.) -- getdatewithouttime() function doesn't exist in MS SQL -- you have to write one
SELECT column1, column2, ..., columnN FROM table WHERE date >= @start
回答by BlaM
The other suggestions are good if you have "SQL only".
如果您只有“SQL”，其他建议也很好。
However I suggest, that - if possible- you calculate the date in your program and insert it as string in the SQL query.
但是我建议，如果可能的话，您可以计算程序中的日期并将其作为字符串插入到 SQL 查询中。
At least for for big tables (i.e. several million rows, maybe combined with joins) that will give you a considerable speed improvement as the optimizer can work with that much better.
至少对于大表（即几百万行，可能与连接相结合），这将为您带来相当大的速度提升，因为优化器可以更好地使用它。
回答by imtheref
argument for DATEADD function :
DATEADD 函数的参数：
DATEADD (*datepart* , *number* , *date* )
datepartcan be: yy, qq, mm, dy, dd, wk, dw, hh, mi, ss, ms
datepart可以是：yy、qq、mm、dy、dd、wk、dw、hh、mi、ss、ms
numberis an expression that can be resolved to an int that is added to a datepart of date
number是一个表达式，可以解析为添加到 date 的 datepart 的 int
dateis an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.
date是可以解析为时间、日期、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式。
回答by Tony
declare @iMonth int
declare @sYear varchar(4)
declare @sMonth varchar(2)
set @iMonth = 0
while @iMonth > -12
begin
set @sYear = year(DATEADD(month,@iMonth,GETDATE()))
set @sMonth = right('0'+cast(month(DATEADD(month,@iMonth,GETDATE())) as varchar(2)),2)
select @sYear + @sMonth
set @iMonth = @iMonth - 1
end
回答by kevinaskevin
I, like @D.E. White, came here for similar but different reasons than the original question. The original question asks for the last 365 days. @samjudson's answer provides that. @D.E. White's answer returns results for the prior calendar year.
我和@DE White 一样，出于与原始问题类似但不同的原因来到这里。原始问题询问过去 365 天。@samjudson 的回答提供了这一点。@DE White 的回答返回上一个日历年的结果。
My query is a bit different in that it works for the prior year up to and including the current date:
我的查询有点不同，因为它适用于前一年（包括当前日期）：
SELECT .... FROM .... WHERE year(date) > year(DATEADD(year, -2, GETDATE()))
SELECT .... FROM .... WHERE year(date) > year(DATEADD(year, -2, GETDATE()))
For example, on Feb 17, 2017 this query returns results from 1/1/2016 to 2/17/2017
例如，在 2017 年 2 月 17 日，此查询返回从 1/1/2016 到 2/17/2017 的结果
