在 SQL Server 中比较没有时间的日期的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9308635/
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
Best way to compare dates without time in SQL Server
提问by Ali
select * from sampleTable
where CONVERT(VARCHAR(20),DateCreated,101)
= CONVERT(VARCHAR(20),CAST('Feb 15 2012 7:00:00:000PM' AS DATETIME),101)
I want to compare date without time
我想比较没有时间的日期
Is above query is ok? or other better solution you suggest
上面的查询可以吗？或您建议的其他更好的解决方案
- I am using SQL Server 2005
- Date saved in UTC format on server
- Users against this data belongs different timezone
- 我正在使用 SQL Server 2005
- 在服务器上以 UTC 格式保存的日期
- 针对此数据的用户属于不同的时区
回答by Marc Gravell
Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOORof the value: this is then just math, not strings - much faster
不要使用 convert - 无缘无故地涉及字符串。一个技巧是日期时间实际上是一个数字，而天数是整数部分（时间是小数部分）；因此，这一天是价值的底线：这只是数学，而不是字符串 - 快得多
declare @when datetime = GETUTCDATE()
select @when -- date + time
declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime)
select @day -- date only
In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed):
在您的情况下，无需转换回日期时间；并使用范围允许最有效的比较（特别是如果索引）：
declare @when datetime = 'Feb 15 2012 7:00:00:000PM'
declare @min datetime = FLOOR(CAST(@when as float))
declare @max datetime = DATEADD(day, 1, @min)
select * from sampleTable where DateCreated >= @min and DateCreated < @max
回答by SubhoM
Simple Cast to Datewill resolve the problem.
Simple Cast toDate将解决问题。
DECLARE @Date datetime = '04/01/2016 12:01:31'
DECLARE @Date2 datetime = '04/01/2016'
SELECT CAST(@Date as date)
SELECT CASE When (CAST(@Date as date) = CAST(@Date2 as date)) Then 1 Else 0 End
回答by user3957458
SELECT .......
FROM ........
WHERE
CAST(@DATETIMEVALUE1 as DATE) = CAST(@DATETIMEVALUE2 as DATE)
The disadvantage is that you are casting the filter column.
缺点是您正在铸造过滤器列。
If there is an index on the filter column, then, since you are casting, the SQL engine can no longer use indexes to filter the date more efficiently.
如果过滤器列上有索引，那么，由于您正在强制转换，SQL 引擎无法再使用索引来更有效地过滤日期。
回答by dknaack
Description
描述
Don't convert your Date to a varchar and compare because string comparisson is not fast.
不要将您的 Date 转换为 varchar 并进行比较，因为字符串比较并不快。
It is much faster if you use >=and <to filter your DateCreatedcolumn.
如果您使用>=和<过滤您的DateCreated列，速度会快得多。
If you have no parameter (like in your sample, a string) you should use the ISO Format <Year><Month><Day>.
如果您没有参数（如您的示例中的字符串），则应使用 ISO 格式<Year><Month><Day>。
Sample
样本
According to your sample
根据您的样品
DECLARE @startDate DateTime
DECLARE @endDate DateTime
SET @startDate = '20120215'
SET @endDate = DATEADD(d,1,@startDate)
SELECT * FROM sampleTable
WHERE DateCreated >= @startDate AND DateCreated < @endDate
More Information
更多信息
回答by rogers.wang
declare @DateToday Date= '2019-10-1'; print @DateToday;
声明@DateToday Date='2019-10-1'; 打印@DateToday;
print Abs(datediff(day, @DateToday,CAST('oct 1 2019 7:00:00:000PM' AS DATETIME))) < 3
打印 Abs(datediff(day, @DateToday,CAST('oct 1 2019 7:00:00:000PM' AS DATETIME))) < 3
this is compare whin 3 days.
这是比较 3 天。
i test this on SQL Server 2014, it works.
我在 SQL Server 2014 上对此进行了测试，它有效。
回答by Oleg Dok
Use 112 CONVERT's format
使用 112 CONVERT 的格式
select *
from sampleTable
where
CONVERT(VARCHAR(20),DateCreated,112)
= CONVERT(VARCHAR(20),CAST('Feb 15 2012 7:00:00:000PM' AS DATETIME),112)
or
或者
if your sql server version 2008+ use DATEtype
如果您的 sql server 版本 2008+ 使用DATE类型
select * from sampleTable
where CONVERT(DATE,DateCreated)
= CONVERT(DATE,CAST('Feb 15 2012 7:00:00:000PM' AS DATETIME))
回答by Ruzbeh Irani
select * from sampleTable
where date_created ='20120215'
This will also compare your column with the particular date without taking time into account
这还将在不考虑时间的情况下将您的列与特定日期进行比较
