如何删除datetime值(SQL Server)的时间部分?

Translate

这是我使用的:

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

我在想可能会有更好,更优雅的方式。

要求:

  • 它必须尽可能快(铸造次数越少越好)。
  • 最终结果必须是datetime类型,而不是字符串。
This question and all comments follow the "Attribution Required."

所有的回答

Translate

SQL Server 2008及更高版本

在SQL Server 2008及更高版本中,当然最快的方法是Convert(date, @date)。这可以回退到datetime要么datetime2如有必要。

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

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

浮点转换不准确

首先,我会远离转换datetimefloat,因为它无法正确转换。您可能无法准确地执行省时的工作,但是我认为使用它是一个坏主意,因为它隐式地告知开发人员这是安全的操作,并且它不是。看一看:

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 ms。然后,转换需要一些额外的金额,因此,如果有两次转换,则该金额将用完大约两次。

更多检查显示,与Convert(, 112)Convert(, 101)查询有一些额外的CPU开销(因为它使用了更长的时间varchar?),因为第二次转换回date花费不及最初转换为varchar,但Convert(, 112)它接近于相同的20000 ms 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
  • 回合是往返的CPU时间datetime.

  • 是一次转换为备用数据类型(具有删除时间部分的副作用)的CPU时间。

  • 基础是从中减去的计算single两种调用之间的区别:single - (round - single)。这是一个粗略的数字,它假定了与该数据类型之间的转换以及datetime在任一方向上大致相同。看起来这个假设并不完美,但很接近,因为所有值都接近20000 ms,只有一个例外。

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

结论

所以看起来像是单向varchar转换方法大约需要1.8μs,并且单向DateDiff方法大约需要0.18μs。我将这基于最保守的“基本CPU”时间,我总共测试了25,920,000行的18458 ms,因此23218 ms / 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)2008年7月9日版权所有(c)1988-2008 Windows NT 5.2(Build 3790:Service Pack 2)上的Microsoft Corporation Standard Edition

来源
Translate

SQL Server 2008有一个新的日期数据类型并将此问题简化为:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
来源
Translate

Itzik Ben-Gan inDATETIME计算,第1部分(SQL Server杂志,2007年2月)显示了执行这种转换的三种方法(最慢到最快;第二和第三种方法之间的差异很小):

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)

您的技术(广播到浮动)是由读者在该杂志的四月号中提出的。据他介绍,它的性能可与上述第二种技术媲美。

来源
Translate

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

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

来源
Translate

请尝试:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
来源
Translate

SQL2005:我建议使用强制转换而不是dateadd。例如,

select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)

平均约10%快点在我的数据集上

select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)

(并且转换为smalldatetime还是更快)

来源