SQL WHERE 子句查找特定月份的所有记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/851236/
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
WHERE Clause to find all records in a specific month
提问by Tarks
I want to be able to give a stored procedure a Month and Year and have it return everything that happens in that month, how do I do this as I can't compare between as some months have different numbers of days etc?
我希望能够为存储过程提供一个月和一年的时间并让它返回该月发生的所有事情，我该怎么做，因为我无法比较，因为有些月份有不同的天数等？
What's the best way to do this? Can I just ask to compare based on the year and month?
做到这一点的最佳方法是什么？我可以要求根据年份和月份进行比较吗？
Thanks.
谢谢。
回答by Shalom Craimer
I think the function you're looking for is MONTH(date). You'll probably want to use 'YEAR'too.
我认为您正在寻找的功能是MONTH(date). 您可能也想使用 “YEAR”。
Let's assume you have a table named thingsthat looks something like this:
假设您有一个名为的表things，如下所示：
id happend_at
-- ----------------
1 2009-01-01 12:08
2 2009-02-01 12:00
3 2009-01-12 09:40
4 2009-01-29 17:55
And let's say you want to execute to find all the records that have a happened_atduring the month 2009/01 (January 2009). The SQL query would be:
假设您要执行以查找happened_at在 2009/01 月（2009 年 1 月）期间具有 a 的所有记录。SQL 查询将是：
SELECT id FROM things
WHERE MONTH(happened_at) = 1 AND YEAR(happened_at) = 2009
Which would return:
哪个会返回：
id
---
1
3
4
回答by Joe
Using the MONTH and YEAR functions as suggested in most of the responses has the disadvantage that SQL Server will not be able to use any index there may be on your date column. This can kill performance on a large table.
使用大多数响应中建议的 MONTH 和 YEAR 函数的缺点是 SQL Server 将无法使用日期列上可能存在的任何索引。这会降低大表的性能。
I would be inclined to pass a DATETIME value (e.g. @StartDate) to the stored procedure which represents the first day of the month you are interested in.
我倾向于将 DATETIME 值（例如 @StartDate）传递给代表您感兴趣的月份的第一天的存储过程。
You can then use
然后你可以使用
SELECT ... FROM ...
WHERE DateColumn >= @StartDate
AND DateColumn < DATEADD(month, 1, @StartDate)
If you must pass the month and year as separate parameters to the stored procedure, you can generate a DATETIME representing the first day of the month using CAST and CONVERT then proceed as above. If you do this I would recommend writing a function that generates a DATETIME from integer year, month, day values, e.g. the following from a SQL Server blog.
如果您必须将月份和年份作为单独的参数传递给存储过程，则可以使用 CAST 和 CONVERT 生成表示该月第一天的 DATETIME，然后按上述操作。如果您这样做，我建议您编写一个函数，从整数年、月、日值生成 DATETIME，例如以下来自SQL Server 博客的内容。
create function Date(@Year int, @Month int, @Day int)
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
go
The query then becomes:
然后查询变为：
SELECT ... FROM ...
WHERE DateColumn >= Date(@Year,@Month,1)
AND DateColumn < DATEADD(month, 1, Date(@Year,@Month,1))
回答by Marcelo Rebou?as
More one tip very simple. You also could use to_charfunction, look:
更一提示很简单。你也可以使用to_char函数，看看：
For Month:
月份：
to_char(happened_at , 'MM') = 01
to_char(happened_at , 'MM') = 01
年份：
to_char(happened_at , 'YYYY') = 2009
to_char(happened_at , 'YYYY') = 2009
日：
to_char(happened_at , 'DD') = 01
to_char(happened_at , 'DD') = 01
to_charfuncion is suported by sql language and not by one specific database.
to_char函数由 sql 语言支持，而不是由一个特定的数据库支持。
I hope help anybody more...
我希望能帮助更多人...
Abs!
腹肌！
回答by Andomar
As an alternative to the MONTH and YEAR functions, a regular WHERE clause will work too:
作为 MONTH 和 YEAR 函数的替代方法，常规 WHERE 子句也可以使用：
select *
from yourtable
where '2009-01-01' <= datecolumn and datecolumn < '2009-02-01'
回答by Gromer
If you're using SQL Server, look into DATEPART.
如果您使用的是 SQL Server，请查看 DATEPART。
http://msdn.microsoft.com/en-us/library/ms174420(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms174420(SQL.90).aspx
DATEPART(mm, [THE DATE YOU'RE LOOKING AT])
DATEPART(mm, [你正在看的日期])
You can then use normal integer logic with it. Same for year, just use yy instead of mm.
然后你可以使用普通的整数逻辑。年份相同，只需使用 yy 而不是 mm。
回答by The Lazy DBA
Can I just ask to compare based on the year and month?
我可以要求根据年份和月份进行比较吗？
You can. Here's a simple example using the AdventureWorks sample database...
你可以。这是一个使用 AdventureWorks 示例数据库的简单示例...
DECLARE @Year INT
DECLARE @Month INT
SET @Year = 2002
SET @Month = 6
SELECT
[pch].*
FROM
[Production].[ProductCostHistory] pch
WHERE
YEAR([pch].[ModifiedDate]) = @Year
AND MONTH([pch].[ModifiedDate]) = @Month
回答by onedaywhen
I find it easier to pass a value as a temporal data type (e.g. DATETIME) then use temporal functionality, specifically DATEADDand DATEPART, to find the start and end dates for the period, in this case the month e.g. this finds the start date and end date pair for the current month, just substitute CURRENT_TIMESTAMPfor you parameter of of type DATETIME(note the 1990-01-01 value is entirely arbitrary):
我发现将值作为时间数据类型传递（例如DATETIME）然后使用时间功能更容易，特别是DATEADD和DATEPART，找到该期间的开始和结束日期，在这种情况下是月份，例如这会找到开始日期和结束日期对对于当前月份，只需替换CURRENT_TIMESTAMP您的类型参数DATETIME（注意 1990-01-01 值完全是任意的）：
SELECT DATEADD(M,
DATEDIFF(M, '1990-01-01T00:00:00.000', CURRENT_TIMESTAMP),
'1990-01-01T00:00:00.000'),
DATEADD(M,
DATEDIFF(M, '1990-01-01T00:00:00.000', CURRENT_TIMESTAMP),
'1990-01-31T23:59:59.997')
回答by Temple
Something like this should do it:
像这样的事情应该这样做：
select * from yourtable where datepart(month,field) = @month and datepart(year,field) = @year
Alternatively you could split month and year out into their own columns when creating the record and then select against them.
或者，您可以在创建记录时将月份和年份拆分为各自的列，然后针对它们进行选择。
Iain
伊恩
回答by Mike Bennett
One way would be to create a variable that represents the first of the month (ie 5/1/2009), either pass it into the proc or build it (concatenate month/1/year). Then use the DateDiff function.
一种方法是创建一个代表一个月的第一天（即 5/1/2009）的变量，将其传递到 proc 或构建它（连接月/1/年）。然后使用 DateDiff 函数。
WHERE DateDiff(m,@Date,DateField) = 0
This will return anything with a matching month and year.
这将返回具有匹配月份和年份的任何内容。
回答by user2022742
SELECT * FROM yourtable WHERE yourtimestampfield LIKE 'AAAA-MM%';
Where AAAAis the year you want and MMis the month you want
AAAA你想要的年份和MM月份在哪里
