SQL - 大于日期查询不起作用

Question

我正在使用下面的 SQL 查询，我只想获取“上次更新日期”列大于或等于 2016 年 1 月 1 日的记录。基本上只有 2016 年的记录。当我使用下面的查询时，我没有收到任何错误，但我仍然收到返回的每个日期，而不仅仅是 2016 年。我的语法有问题吗？

 SELECT
  NPI,
  [Entity Type Code],
  [Replacement NPI],
  [Employer Identification Number (EIN)],
  [Provider Organization Name (Legal Business Name)],
  [Provider Last Name (Legal Name)],
  [Provider First Name],
  [Provider Middle Name],
  [Provider Name Prefix Text],
  [Provider Name Suffix Text],
  [Provider Credential Text],
  [Provider Other Organization Name],
  [Provider Other Organization Name Type Code],
  [Provider Other Last Name],
  [Provider Other First Name],
  [Provider Other Middle Name],
  [Provider Other Name Prefix Text],
  [Provider Other Name Suffix Text],
  [Provider Other Credential Text],
  [Provider Other Last Name Type Code],
  [Provider First Line Business Mailing Address],
  [Provider Second Line Business Mailing Address],
  [Provider Business Mailing Address City Name],
  [Provider Business Mailing Address State Name],
  [Provider Business Mailing Address Postal Code],
  [Provider Business Mailing Address Country Code (If outside U S )],
  [Provider Business Mailing Address Telephone Number],
  [Provider Business Mailing Address Fax Number],
  [Provider First Line Business Practice Location Address],
  [Provider Second Line Business Practice Location Address],
  [Provider Business Practice Location Address City Name],
  [Provider Business Practice Location Address State Name],
  [Provider Business Practice Location Address Postal Code],
  [Provider Business Practice Location Address Country Code (If outside U S )],
  [Provider Business Practice Location Address Telephone Number],
  [Provider Business Practice Location Address Fax Number],
  [Provider Enumeration Date],
  [Last Update Date]
FROM Data
WHERE ([Healthcare Provider Taxonomy Code_1] IN ('122300000X', '1223G0001X'))
AND ([Last Update Date] >= '01/01/2016')

原文由 user1342164 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

使用以下内容代替 ([Last Update Date] >= '01/01/2016') ：

YEAR( [Last Update Date]) = 2016

获取 2016 年更新的所有记录

原文由 athina 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

用这个：

 WHERE CONVERT(datetime, [Last Update Date], 101) >= '2016-01-01'

请注意，您想要的格式掩码是 101 ，它对应于格式为 mm/dd/yyyy 的数据（参见 文档）。这假设您以这种格式存储日期，并在比较时通过您在原始 WHERE 子句中使用的格式来说明。

另请注意，您不需要在上述表达式中比较的 RHS 上使用 CONVERT ，因为假设日期文字采用标准格式，SQL Server 应该能够处理它。

原文由 Tim Biegeleisen 发布，翻译遵循 CC BY-SA 3.0 许可协议