如何删除日期时间值的时间部分(SQL Server)？

How to remove the time portion of a datetime value (SQL Server)?

这是我使用的：

1	SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)

我认为可能会有更好，更优雅的方式。

要求：

它必须尽可能快(转换越少越好)。
最终结果必须是datetime类型，而不是字符串。

SQL Server 2008及更高版本

在SQL Server 2008及更高版本中，当然最快的方法是Convert(date, @date)。如有必要，可以将其转换回datetime或datetime2。

在SQL Server 2005及更高版本中，什么才是真正的最佳选择？

我见过关于在SQL Server中从日期开始缩短时间最快的说法不一致，甚至有人说他们做了测试，但是我的经验有所不同。因此，让我们进行一些更严格的测试，让每个人都拥有脚本，以便在遇到任何错误时人们都可以纠正我。

浮点转换不准确

首先，我不会将datetime转换为float，因为它不能正确转换。您可能无法准确地进行时间删除操作，但是我认为使用它不是一个好主意，因为它隐式地向开发人员传达了这是安全的操作，而不是安全的。看一看：

1
2
3
4

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

这不是我们应该在我们的代码或在线示例中教给人们的东西。

而且，它甚至不是最快的方法！

证明–性能测试

如果您想自己执行一些测试以查看不同方法的实际叠加情况，那么您将需要以下安装脚本来进一步运行测试：

1
2
3
4
5
6
7
8
9
10
11
12
13

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.57MB的表，并且将花费大约15-30分钟的时间来运行。如果您的数据库很小并且设置为10％的增长，则比首先确定足够大的时间要花费更长的时间。

现在为实际性能测试脚本。请注意，不要将行返回给客户端是有目的的，因为这在2600万行上非常昂贵，并且会隐藏方法之间的性能差异。

绩效结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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子句中：

1
2
3

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使用率的某些部分不涉及日期计算，在上述查询中，这似乎接近19875ms。然后，转换需要花费一些额外的金额，因此，如果有两次转换，则该金额将用完大约两次。

更多检查发现，与Convert(, 112)相比，Convert(, 101)查询有一些额外的CPU开销(因为它使用了更长的varchar？)，因为第二次转换回date的花费不及初始转换的费用??。转换为varchar，但使用Convert(, 112)则更接近相同的20000ms CPU基本成本。

这是我用于上述分析的有关CPU时间的计算：

1
2
3
4
5
6
7
8

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之间的转换在任一方向上大致相同。看起来这个假设并不完美，但是很接近，因为所有值都接近20000ms，只有一个例外。

更有趣的是，基本成本几乎等于单个Convert(date)方法(成本几乎为0，因为服务器可以在内部从datetime的前四个字节中提取整数天部分)数据类型)。

结论

因此，单向varchar转换方法大约需要1.8μs的时间，而单向DateDiff方法则大约需要0.18μs的时间。我将这基于最保守的"基本CPU"时间，在我对25,920,000行的总共18458ms的测试中，因此23218ms / 25920000 =0.18μs。明显的10倍改进似乎很多，但坦率地说，它很小，直到您要处理成千上万的行(617k行= 1秒节省)。

在我看来，即使给出了这样的绝对改进，DateAdd方法还是有优势的，因为它是性能和清晰度的最佳组合。需要0.50000004的"幻数"的答案总有一天会咬人(五个零或六个???)，而且很难理解。

补充笔记

当我有一些时间时，我将把0.50000004更改为'12:00:00.003'并查看其效果。它被转换为相同的datetime值，我发现它更容易记住。

对于那些感兴趣的人，以上测试是在服务器上运行的，其中@@ Version返回以下内容：

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)

Ok.

好。

SQL Server 2008具有新的日期数据类型，此问题简化为：

1	SELECT CAST(CAST(GETDATE() AS date) AS datetime)

Itzik Ben-Gan在DATETIME Calculations，第1部分(SQL Server Magazine，2007年2月)中介绍了执行这种转换的三种方法(最慢到最快；第二种和第三种方法之间的差异很小)：

1
2
3
4
5

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)

读者在杂志的四月号中提出了您的技术(铸造成浮动)。据他介绍，它的性能可与上述第二种技术媲美。

您的CAST-FLOOR-CAST似乎已经是最佳方法，至少在MS SQL Server 2005上是如此。

我见过的其他一些解决方案具有字符串转换功能，例如其中的Select Convert(varchar(11), getdate(),101)，它的速度慢了10倍。

请试试：