SQL 比较 DATETIME 和 DATE 忽略时间部分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1843395/
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 DATETIME and DATE ignoring time portion
提问by abatishchev
I have two tables where column [date]is type of DATETIME2(0).
我有两个表，其中列[date]的类型为DATETIME2(0).
I have to compare two records only by theirs Date parts (day+month+year), discarding Time parts (hours+minutes+seconds).
我必须仅按日期部分（日+月+年）比较两条记录，丢弃时间部分（小时+分钟+秒）。
How can I do that?
我怎样才能做到这一点？
回答by marc_s
Use the CASTto the new DATEdata type in SQL Server 2008 to compare just the date portion:
使用SQL Server 2008 中CAST的新DATE数据类型仅比较日期部分：
IF CAST(DateField1 AS DATE) = CAST(DateField2 AS DATE)
回答by Disillusioned
A small drawback in Marc's answer is that both datefields have been typecast, meaning you'll be unable to leverage any indexes.
Marc 回答中的一个小缺点是两个日期字段都已被类型转换，这意味着您将无法利用任何索引。
So, if there is a need to write a query that can benefit from an index on a date field, then the following (rather convoluted) approach is necessary.
因此，如果需要编写一个可以从日期字段上的索引中受益的查询，那么以下（相当复杂的）方法是必要的。
- The indexed datefield (call it DF1) must be untouched by any kind of function.
- So you have to compare DF1 to the full range of datetime values for the day of DF2.
- That is from the date-part of DF2, to the date-part of the day after DF2.
- I.e.
(DF1 >= CAST(DF2 AS DATE)) AND (DF1 < DATEADD(dd, 1, CAST(DF2 AS DATE))) - NOTE: It is very important that the comparison is >=(equality allowed) to the date of DF2, and (strictly) <the day after DF2. Also the BETWEEN operator doesn't work because it permits equality on both sides.
- 索引日期字段（称为 DF1）必须不受任何类型的函数影响。
- 因此，您必须将 DF1 与 DF2 当天的所有日期时间值进行比较。
- 即从 DF2 的日期部分到 DF2 之后的日期部分。
- IE
(DF1 >= CAST(DF2 AS DATE)) AND (DF1 < DATEADD(dd, 1, CAST(DF2 AS DATE))) - 注意：非常重要的是，比较>=（允许相等）到 DF2 的日期，并且（严格地）<DF2 之后的一天。BETWEEN 运算符也不起作用，因为它允许双方相等。
PS: Another means of extracting the date only (in older versions of SQL Server) is to use a trick of how the date is represented internally.
PS：另一种仅提取日期的方法（在旧版本的 SQL Server 中）是使用内部表示日期的技巧。
- Cast the date as a float.
- Truncate the fractional part
- Cast the value back to a datetime
- I.e.
CAST(FLOOR(CAST(DF2 AS FLOAT)) AS DATETIME)
- 将日期转换为浮点数。
- 截断小数部分
- 将值转换回日期时间
- IE
CAST(FLOOR(CAST(DF2 AS FLOAT)) AS DATETIME)
回答by pimbrouwers
Though I upvoted the answer marked as correct. I wanted to touch on a few things for anyone stumbling upon this.
虽然我赞成标记为正确的答案。我想为任何遇到此问题的人谈一些事情。
In general, if you're filtering specifically on Datevalues alone. Microsoft recommends using the language neutral format of ymdor y-m-d.
通常，如果您仅针对日期值进行专门过滤。微软建议使用的语言无关的格式ymd或y-m-d。
Note that the form '2007-02-12' is considered language-neutral only for the data types DATE, DATETIME2, and DATETIMEOFFSET.
请注意，表单“2007-02-12”仅对于数据类型 DATE、DATETIME2 和 DATETIMEOFFSET 被视为与语言无关。
To do a date comparison using the aforementioned approach is simple. Consider the following, contrived example.
使用上述方法进行日期比较很简单。考虑以下人为设计的示例。
--112 is ISO format 'YYYYMMDD'
declare @filterDate char(8) = CONVERT(char(8), GETDATE(), 112)
select
*
from
Sales.Orders
where
CONVERT(char(8), OrderDate, 112) = @filterDate
In a perfect world, performing any manipulation to the filtered column should be avoided because this can prevent SQL Server from using indexes efficiently. That said, if the data you're storing is only ever concerned with the date and not time, consider storing as DATETIMEwith midnight as the time. Because:
在理想情况下，应该避免对过滤列执行任何操作，因为这会阻止 SQL Server 有效地使用索引。也就是说，如果您存储的数据只涉及日期而不是时间，请考虑DATETIME以午夜作为时间进行存储。因为：
When SQL Server converts the literal to the filtered column's type, it assumes midnight when a time part isn't indicated. If you want such a filter to return all rows from the specified date, you need to ensure that you store all values with midnight as the time.
当 SQL Server 将文字转换为筛选列的类型时，如果未指示时间部分，则假定为午夜。如果您希望这样的过滤器返回指定日期的所有行，您需要确保以午夜为时间存储所有值。
Thus, assuming you are only concerned with date, and store your data as such. The above query can be simplified to:
因此，假设您只关心日期，并按原样存储您的数据。上面的查询可以简化为：
--112 is ISO format 'YYYYMMDD'
declare @filterDate char(8) = CONVERT(char(8), GETDATE(), 112)
select
*
from
Sales.Orders
where
OrderDate = @filterDate
回答by reza.cse08
You can try this one
你可以试试这个
CONVERT(DATE, GETDATE()) = CONVERT(DATE,'2017-11-16 21:57:20.000')
I test that for MS SQL 2014 by following code
我通过以下代码测试 MS SQL 2014
select case when CONVERT(DATE, GETDATE()) = CONVERT(DATE,'2017-11-16 21:57:20.000') then 'ok'
else '' end
回答by pankaj
For Compare two date like MM/DD/YYYYto MM/DD/YYYY. Remember First thing column type of Field must be dateTime. Example : columnName : payment_datedataType : DateTime.
对于比较两个日期，如MM/DD/YYYY到MM/DD/YYYY。记住 Field 的第一件事列类型必须是 dateTime。示例：columnName： payment_date数据类型：DateTime。
after that you can easily compare it. Query is :
之后，您可以轻松比较它。查询是：
select * from demo_date where date >= '3/1/2015' and date <= '3/31/2015'.
It very simple ...... It tested it.....
很简单......它测试了它......
