SQL Server 中仅对年份进行日期时间查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8654627/
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
DateTime query on only year in SQL Server
提问by user1074474
I want to select multiple records on the basis of a matching year, for example in table tab
where columns are [id] int, [name] varchar, [bookyear] datetime
我想根据匹配的年份选择多条记录，例如在表选项卡中，其中的列是[id] int, [name] varchar,[bookyear] datetime
I want to select all records where the year is 2009.
我想选择年份为 2009 的所有记录。
The following query gives 0 results:
以下查询给出 0 个结果：
SELECT [ACCNO]
,[Roll No]
,[IssueDate]
,[DueDate]
FROM [test1].[dbo].[IssueHis$]
where [IssueDate] between 12-12-2004 and 1-01-2010
回答by dotnetstep
select id,name,bookyear from tab1 where year(bookyear) = 2009
回答by Mikael Eriksson
SELECT [ACCNO]
,[Roll No]
,[IssueDate]
,[DueDate]
FROM [test1].[dbo].[IssueHis$]
WHERE [IssueDate] >= '20090101' AND
[IssueDate] < '20100101'
回答by gbn
- You need to delimit datetime constants like you would a string
- Use
yyyymmddpattern for SQL Server (notyyyy-mm-dd, it is unsafe generally) - Use proper range queries to allow for time value
- 您需要像分隔字符串一样分隔日期时间常量
yyyymmdd对 SQL Server使用模式（不是yyyy-mm-dd，通常是不安全的）- 使用适当的范围查询以允许时间值
So, to find values between "12 Dec 2004" and "31 Dec 2009" inclusive, allowing for time:
所以，“2004年12月12日”和“2009年12月31日”之间找到价值包容，允许时间：
...
where [IssueDate] >= '20041212' AND [IssueDate] < '20100101'
Edit, or this, because of the ambiguity in the question for "year = 2009"
编辑，或者这个，因为“year = 2009”的问题含糊不清
...
where [IssueDate] >= '20090101' AND [IssueDate] < '20100101'
回答by gbn
One of the most important things to remember with regards to SQL Server and dates is that the best format to use is this: 'YYYYMMDD'(or 'YYYYMMDD HH:MI:SS'). This format will always be interpreted properly by SQL Server, regardless of regional date settings.
关于 SQL Server 和日期，需要记住的最重要的事情之一是使用的最佳格式是：（'YYYYMMDD'或'YYYYMMDD HH:MI:SS'）。无论区域日期设置如何，SQL Server 将始终正确解释此格式。
SELECT [ACCNO]
,[Roll No]
,[IssueDate]
,[DueDate]
FROM [test1].[dbo].[IssueHis$]
WHERE [IssueDate] >= '20090101'
AND [IssueDate] < '20100101'
回答by Michael Riley - AKA Gunny
You could create a view that has the IssueYearas a separate column
您可以创建一个将IssueYear作为单独列的视图
CREATE VIEW vIssueHis
AS
SELECT
[ACCNO],
[Roll No],
[IssueDate],
[DueDate],
DATEPART(yyyy,IssueDate) as IssueYear,
DATEPART(yyyy,DueDate) as DueYear
FROM [test1].[dbo].[IssueHis$]Then you could query the view like this
然后你可以像这样查询视图
SELECT [ACCNO]
,[Roll No]
,[IssueDate]
,[DueDate]
FROM vIssueHis
WHERE IssueYear = 2009回答by iYazee6
You can do
你可以做
....
WHERE [IssueDate] > '2004' AND [IssueDate] < 2010
