SQL 仅使用每年的月份将当前日期与存储的日期时间进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/547512/
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
Compare current date with stored datetime using month an year only
提问by Ryan Emerle
Using SQL Server 2005 I have a field that contains a datetime value.
使用 SQL Server 2005 我有一个包含日期时间值的字段。
What I am trying to do is create 2 queries:
我想要做的是创建 2 个查询：
- Compare to see if stored datetime is of the same month+year as current date
- Compare to see if stored datetime is of the same year as current date
- 比较以查看存储的日期时间是否与当前日期同月+同年
- 比较以查看存储的日期时间是否与当前日期为同一年
There is probably a simple solution but I keep hitting brick walls using various samples I can find, any thoughts?
可能有一个简单的解决方案，但我一直在使用我能找到的各种样品击打砖墙，有什么想法吗？
Thanks in advance.
提前致谢。
回答by Ryan Emerle
Compare the parts of the date:
比较日期的各个部分：
WHERE YEAR( columnName ) = YEAR( getDate() )
回答by Taylor Gerring
While the other answers will work, they all suffer from the same problem: they apply a transformation to the column and therefore will never utilize an index on that column.
虽然其他答案有效，但它们都遇到相同的问题：它们对列应用转换，因此永远不会在该列上使用索引。
To search the date without a transformation, you need a couple built-in functions and some math. Example below:
要在不进行转换的情况下搜索日期，您需要一些内置函数和一些数学运算。下面的例子：
--create a table to hold our example values
create table #DateSearch
(
TheDate datetime not null
)
insert into #DateSearch (TheDate)
--today
select getdate()
union all
--a month in advance
select dateadd(month, 1, getdate())
union all
--a year in advance
select dateadd(year, 1, getdate())
go
--declare variables to make things a little easier to see
declare @StartDate datetime, @EndDate datetime
--search for "same month+year as current date"
select @StartDate = dateadd(month, datediff(month, 0, getdate()), 0), @EndDate = dateadd(month, datediff(month, 0, getdate()) + 1, 0)
select @StartDate [StartDate], @EndDate [EndDate], TheDate from #DateSearch
where TheDate >= @StartDate and TheDate < @EndDate
--search for "same year as current date"
select @StartDate = dateadd(year, datediff(year, 0, getdate()), 0), @EndDate = dateadd(year, datediff(year, 0, getdate()) + 1, 0)
select @StartDate [StartDate], @EndDate [EndDate], TheDate from #DateSearch
where TheDate >= @StartDate and TheDate < @EndDate
What the statement does to avoid the transformations, is find all values greater-than or equal-to the beginning of the current time period (month or year) AND all values less-than the beginning of the next (invalid) time period. This solves our index problem and also mitigates any issues related to 3ms rounding in the DATETIME type.
该语句为避免转换所做的是查找大于或等于当前时间段（月或年）开始的所有值以及小于下一个（无效）时间段开始的所有值。这解决了我们的索引问题，并缓解了与 DATETIME 类型中 3 毫秒舍入相关的任何问题。
回答by LukeH
回答by LukeH
SELECT * FROM atable
WHERE
YEAR( adate ) = YEAR( GETDATE() )
AND
MONTH( adate ) = MONTH( GETDATE() )
回答by Tommy Hui
You can use something like this
你可以使用这样的东西
a)
一种）
select *
from table
where MONTH(field) = MONTH(GetDATE())
and YEAR(field) = YEAR(GetDATE())
b)
b)
select *
from table
where YEAR(field) = YEAR(GetDATE())
回答by user53794
The datepart function lets you pull the bits you need:
datepart 函数可让您提取所需的位：
declare @d1 as datetime
declare @d2 as datetime
if datepart(yy, @d1) = datepart(yy, @d2) and datepart(mm, @d1) = datepart(mm, @d2) begin
print 'same'
end
