这是我使用的：
SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)
我在思考可能有更好、更优雅的方法。
要求：
- 尽可能快（转换次数越少越好）
- 最终结果必须是
datetime类型，而不是字符串。
如何从日期时间值中删除时间部分（SQL Server）？
86
- Nathan Bedford
6个回答
118
SQL Server 2008及以上版本
在SQL Server 2008及以上版本中，当然最快的方法是 Convert(date, @date)。如果需要，可以将其转换回datetime或datetime2。
在SQL Server 2005和更早版本中，到底什么是最好的选择？
我看到了一些有关在SQL Server中截取日期时间的最快方法的不一致说法，有些人甚至说他们进行了测试，但我的经验并不相同。因此，让我们进行更严格的测试，并让每个人都拥有该脚本，以便如果我犯了任何错误，人们可以纠正我。
浮点数转换不准确
首先，我会避免将datetime转换为float，因为它不会正确地进行转换。您可能可以通过准确地删除时间来解决这个问题，但我认为使用它是一个坏主意，因为它会隐含地向开发人员传达这是一个安全操作，实际上并不是。看一下：
declare @d datetime;
set @d = '2010-09-12 00:00:00.003';
select Convert(datetime, Convert(float, @d));
-- result: 2010-09-12 00:00:00.000 -- oops
这不是我们应该在我们的代码或在线示例中教授给人们的内容，而且它也不是最快的方式！
证明 - 性能测试
如果您想自己执行一些测试以查看不同方法的实际表现如何，那么您将需要此设置脚本才能运行下面的测试:
create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);
declare @d datetime;
set @d = DateDiff(Day, 0, GetDate());
insert AllDay select @d;
while @@ROWCOUNT != 0
insert AllDay
select * from (
select Tm =
DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm)
from AllDay
) X
where Tm < DateAdd(Day, 1, @d);
exec sp_spaceused AllDay; -- 25,920,000 rows
请注意，这会在您的数据库中创建一个大小为427.57 MB的表，并且需要大约15-30分钟才能运行。如果您的数据库很小并且设置为10％增长，则需要更长时间，而如果您首先足够大，则需要更短的时间。
现在是实际性能测试脚本。请注意，不返回行到客户端是有意义的，因为在2600万行上这是非常昂贵的，并且会隐藏方法之间的性能差异。
性能结果
set statistics time on;
-- (All queries are the same on io: logical reads 54712)
GO
declare
@dd date,
@d datetime,
@di int,
@df float,
@dv varchar(10);
-- Round trip back to datetime
select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms, elapsed time = 22301 ms.
select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms.
select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms.
select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms.
select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms.
select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms, elapsed = 108236 ms.
select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms.
-- Only to another type but not back
select @dd = Tm from AllDay; -- CPU time = 19891 ms, elapsed time = 20937 ms.
select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms.
select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms
select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms.
select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms.
select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms, elapsed = 67987 ms.
select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms.
GO
set statistics time off;
一些杂乱的分析
关于这个问题，有几点需要注意。首先，如果只是执行 GROUP BY 或比较操作，就没有必要将时间转换回 datetime 格式。因此，您可以通过避免这样做来节省一些 CPU，除非您需要最终值进行显示。您甚至可以按未转换的值进行 GROUP BY，并仅在 SELECT 子句中进行转换:
select Convert(datetime, DateDiff(dd, 0, Tm))
from (select '2010-09-12 00:00:00.003') X (Tm)
group by DateDiff(dd, 0, Tm)
另外，看一下数字转换回 datetime 所需的时间，只比字符串 varchar 转换多一点，但是字符串转换几乎增加了一倍的时间？这揭示了查询中用于日期计算的 CPU 占用部分。CPU 使用情况中有一些不涉及日期计算的部分，在上述查询中大约为19875毫秒左右。然后转换需要额外的一些时间，所以如果有两个转换，那么该数量将被使用大约两次。
进一步检查发现，与 Convert(, 112) 相比，Convert(, 101) 查询具有额外的 CPU 费用（因为它使用了更长的 varchar？），因为第二次转换回 date 的成本不像初始转换为 varchar 那样昂贵，但是在 Convert(, 112) 中，它接近相同的20000毫秒 CPU 基础成本。
这里是我用于上述分析的 CPU 时间计算：
method round single base
----------- ------ ------ -----
date 21324 19891 18458
int 23031 21453 19875
datediff 23782 23218 22654
float 36891 29312 21733
varchar-112 102984 64016 25048
varchar-101 123375 65609 7843
round 代表从
datetime转换为另一种数据类型再转回来的 CPU 时间。single 代表将日期时间值转换为另一种数据类型的 CPU 时间（这种转换会导致日期时间值失去时间部分）。
base 是通过从
single中减去两次调用之间的差异来计算的：single - (round - single)。这是一个大概的数字，它假定转换到和从该数据类型和datetime的转换是相同的方向且大致相等。它看起来并不完美，但是与仅有一个例外的所有值都接近于 20000 毫秒。
还有一件有趣的事情是基准成本几乎等于单个 Convert(date) 方法（其成本几乎为 0，因为服务器可以从 datetime 数据类型的前四个字节中内部提取整数天部分）。
结论
看起来单向 varchar 转换方法大约需要 1.8 微秒，而单向 DateDiff 方法需要大约 0.18 微秒。我根据我的测试中最保守的“基础 CPU”时间，即 25920000 行的总计 18458 毫秒来计算（23218 毫秒 / 25920000 = 0.18 微秒）。这个表现出的 10 倍提高似乎很大，但直到处理成千上万行数据时才显得相对较小（617k 行=1 秒的节省）。
即使在绝对改进很小的情况下，我认为 DateAdd 方法获胜，因为它是性能和清晰度的最佳组合。那些需要“神奇数字”0.50000004 的答案最终会让某个人付出代价（五个零还是六个？？？），而且更难理解。
附加说明
当我有时间时，我将把 0.50000004 更改为 '12:00:00.003' 并看看结果如何。它被转换为相同的 datetime 值，我觉得更容易记住。
对于那些感兴趣的人，以上测试是在一个返回以下内容的服务器上运行的：
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
- ErikE
12
30
SQL Server 2008有一个新的日期数据类型，这使得该问题变得简单：
SELECT CAST(CAST(GETDATE() AS date) AS datetime)
- Marek Grzenkowicz
1
1我错误地输入了0218而不是2018作为年份，导致
DATEADD(DATEDIFF())方法在去除时间部分时抛出异常。当我将结果强制转换回datetime2时，您的方法可以很好地工作：select cast(CAST(convert(datetime2(0), '0218-09-12', 120) AS date) as datetime2)。 - Bernhard Döbler17
在《SQL Server Magazine》2007年2月的文章DATETIME Calculations, Part 1中，Itzik Ben-Gan展示了执行日期时间转换的三种方法（从最慢到最快；第二种和第三种方法之间的差异很小）：
SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
SELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime)
读者在该杂志的四月份建议将技术（转换为float）用于性能，他表示它与上面介绍的第二种技术相当。
- Marek Grzenkowicz
6
1@Emtucifor 我同意第三种方法非常晦涩，因为它使用了 0.50000004 的值，但是它是最快的，而且你的测试也证实了这一点。因此，它满足了“尽可能快”的要求。 - Marek Grzenkowicz
1@Emtucifor，此外，我链接的文章中提到了关于0.50000004值的内容：“尽管这个表达式很短（而且效率高，我马上就会证明），但我必须说我对它感到不安。我不确定我能准确地说明原因——也许是因为它太技术化了，你看不到与日期时间相关的逻辑。” - Marek Grzenkowicz
2如果我们要使用这种方法，我更喜欢使用
SELECT CAST(CAST(GETDATE() - '12:00:00.003' AS int) AS datetime)，因为它对我有意义，而且我认为更容易记住。 - ErikE6这是 SQL 2008 中最快的方式：
Convert(date, GetDate())。 - ErikE1“Datetime Calculations, Part 1” 文章的正确链接现在是 https://www.itprotoday.com/analytics-and-reporting/datetime-calculations-part-1。 - Antonio Garcia Marin
12
你的CAST-FLOOR-CAST已经似乎是最佳方式，至少在MS SQL Server 2005上。
我看到其他一些解决方案中有字符串转化，例如Select Convert(varchar(11), getdate(),101)，但这种方法会慢10倍。
- Michael Stum
2
1我们在其中一个产品中使用了Michael Stum建议的方法，它非常有效。 - Chris Roberts
4
请尝试：
SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
- srihari
1
SQL2005: 我建议使用cast而不是dateadd。例如，
select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)
平均来看，它在我的数据集上比原来快大约10％。
select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)
（将其）转换为smalldatetime类型更快。
- user4217069
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
char而不是varchar，是否会有时间上的差异？ - Gabe