如何清除datetime值的时间部分（SQL Server）？ [英] How to remove the time portion of a datetime value (SQL Server)?

问题描述

这是我使用的：

  SELECT CAST（FLOOR（CAST（getdate（）as FLOAT））as DATETIME）
  

我认为可能会有一个更好和更优雅的方式。

要求：

• 必须尽可能快（铸件越少越好） li>
  
• 最终结果必须是 datetime 类型，而不是字符串。

解决方案

SQL Server 2008及更高版本

在SQL Server 2008当然最快的方法是 Convert（date，@date）。如有必要，可以将其退回到 datetime 或 datetime2 。

什么是最好的？

我已经看到有关截断SQL Server中的日期时间最快的声明不一致有些人甚至说他们做过测试，但是我的经验是不一样的。所以让我们做一些更严格的测试，让每个人都有这样的脚本，如果我犯错误，人们可以纠正我。

浮动转换不准确

首先，我将远离将 datetime 转换为 float ，因为它不能正确转换。您可能会准确地执行脱机操作，但我认为使用它是一个坏主意，因为它隐含地向开发者传达这是一个安全的操作，而它不是。看看：

  declare @d datetime; 
 set @d ='2010-09-12 00：00：00.003'; 
选择Convert（datetime，Convert（float，@d））; 
  - 结果：2010-09-12 00：00：00.000  -  oops 
  

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

此外，它甚至不是最快的方式！

证明 - 性能测试

如果你想自己执行一些测试，看看不同的方法真的如何堆栈起来，那么你需要这个安装脚本来运行更远的测试：

  create table AllDay（Tm datetime NOT NULL CONSTRAINT PK_AllDay主键集合）; 
 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））从AllDay $ 3，Tm）
来自AllDay 
）X 
其中Tm < DateAdd（Day，1，@d）; 
 exec sp_spaceused AllDay; -  25,920,000行
  

请注意，这将在您的数据库中创建一个427.57的MB表，并将像15-30分钟的跑步。如果您的数据库很小，并且设置为10％的增长速度，将比您先前足够大的时间花费更长时间。

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

性能结果

 设置统计时间; 
  - （所有查询在io上相同：逻辑读数54712）
 GO 
声明
 @dd日期，
 @d datetime，
 @ di int，
 @df float，
 @dv varchar（10）; 
 
  - 往返于datetime 
从AllDay中选择@d = CONVERT（date，Tm）; -  CPU时间= 21234 ms，经过时间= 22301 ms。 
从AllDay中选择@d = CAST（Tm  -  0.50000004 AS int）; -  CPU = 23031 ms，已用= 24091 ms。 
从AllDay中选择@d = DATEDIFF（DAY，0，Tm）; -  CPU = 23782 ms，已用= 24818 ms。 
从AllDay中选择@d = FLOOR（CAST（Tm as float）） -  CPU = 36891 ms，已用= 38414 ms。 
从AllDay中选择@d = CONVERT（VARCHAR（8），Tm，112）; -  CPU = 102984 ms，已用= 109897 ms。 
从AllDay中选择@d = CONVERT（CHAR（8），Tm，112）; -  CPU = 103390 ms，经过= 108236 ms。 
从AllDay中选择@d = CONVERT（VARCHAR（10），Tm，101）; -  CPU = 123375 ms，经过= 135179 ms。 
 
  - 只有另一个类型，但不回来
从AllDay中选择@dd = Tm; -  CPU时间= 19891 ms，经过时间= 20937 ms。 
从AllDay中选择@di = CAST（Tm  -  0.50000004 AS int）; -  CPU = 21453 ms，已用= 23079 ms。 
从AllDay中选择@di = DATEDIFF（DAY，0，Tm）; -  CPU = 23218 ms，已用= 24700 ms 
从AllDay中选择@df = FLOOR（CAST（Tm as float）） -  CPU = 29312 ms，已用= 31101 ms。 
从AllDay中选择@dv = CONVERT（VARCHAR（8），Tm，112）; -  CPU = 64016 ms，经过= 67815 ms。 
从AllDay中选择@dv = CONVERT（CHAR（8），Tm，112）; -  CPU = 64297 ms，已用= 67987 ms。 
从AllDay中选择@dv = CONVERT（VARCHAR（10），Tm，101）; -  CPU = 65609 ms，已用= 68173 ms。 
 GO 
设置统计时间关闭; 
  

一些漫游分析

有关这方面的注意事项。首先，如果只执行GROUP BY或比较，则不需要转换回 datetime 。所以你可以通过避免这样做来节省一些CPU，除非你需要最终的值才能显示。您甚至可以将GROUP BY转换为未转换的值，并将转换仅在SELECT子句中：

 选择Convert（datetime，DateDiff（dd ，0，Tm））
从（选择'2010-09-12 00：00：00.003'）X（Tm）
 group by DateDiff（dd，0，Tm）
  

另外，请看数字转换只需要稍微更多的时间转换回 datetime ，但 varchar 转换几乎翻倍？这显示了在查询中专门用于日期计算的CPU的部分。 CPU使用的部分内容不涉及日期计算，在上述查询中似乎接近19875 ms。那么转换需要一些额外的金额，所以如果有两次转换，那么这个金额大约用了两次。

更多的检查显示，与转换（，112）， Convert（，101）查询有一些额外的CPU费用（因为它使用更长的 varchar ？），因为第二次转换回 date 不会像初始转换为 varchar ，但使用转换（，112）它更接近相同的20000 ms CPU基础成本。

以下是我用于上述分析的CPU时间的计算：

 方法round single base 
 ----------- ------ ------ ----- 
日期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 是一轮的CPU时间返回到 datetime 。




• 单个是单个CPU的CPU时间转换为备用数据类型（具有删除时间部分的副作用）。




• base 是计算从单个中减去两个调用之间的差异： single - （round-single）。它是一个ballpark图，假设转换到和从该数据类型，并且 datetime 在任一方向大致相同。似乎这个假设并不完美，但是很接近，因为只有一个例外，这些值都接近20000 ms。

<另一个有趣的事情是，基本费用几乎等于单个 Convert（date）方法（这几乎是0成本，因为服务器可以在内部提取整数天部分正好位于 datetime 数据类型的前四个字节之内）。

结论

所以看来，单向 varchar 转换方法大概需要1.8 ;μs和单向 DateDiff 方法大约需要0.18微秒。在我测试18458总共25,920,000行的时候，我基于最保守的基本CPU时间，因此23218  ms / 25920000 = 0.18 μs。显然10倍的改善似乎很多，但直到你处理数十万行（617k行= 1秒储蓄）时，它坦然相当小。

甚至鉴于这个小的绝对改进，在我看来， DateAdd 方法胜出，因为它是性能和清晰度的最佳组合。需要 0.50000004 的魔术数字的答案是会在某天（五个零或六个???）中咬人，加上更难理解。

其他注释

当我有一段时间我要更改 0.50000004 to '12：00：00.003'，看看它是如何实现的。它被转换为相同的 datetime 值，我发现它更容易记住。

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

Microsoft SQL Server 2008（RTM） - 10.0.1600.22（Intel X86）2008年7月9日14:43:34版权所有（c）1988-2008 Windows NT 5.2（Build 3790：Service Pack 2）上的Microsoft Corporation标准版

Here's what I use:

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

I'm thinking there may be a better and more elegant way.

Requirements:

• It has to be as fast as possible (the less casting, the better).
• The final result has to be a datetime type, not a string.

解决方案

SQL Server 2008 and up

In SQL Server 2008 and up, of course the fastest way is Convert(date, @date). This can be cast back to a datetime or datetime2 if necessary.

What Is Really Best?

I've seen inconsistent claims about what's fastest for truncating the time from a date in SQL Server, and some people even said they did testing, but my experience has been different. So let's do some more stringent testing and let everyone have the script so if I make any mistakes people can correct me.

Float Conversions Are Not Accurate

First, I would stay away from converting datetime to float, because it does not convert correctly. You may get away with doing the time-removal thing accurately, but I think it's a bad idea to use it because it implicitly communicates to developers that this is a safe operation and it is not. Take a look:

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

This is not something we should be teaching people in our code or in our examples online.

Also, it is not even the fastest way!

Proof – Performance Testing

If you want to perform some tests yourself to see how the different methods really do stack up, then you'll need this setup script to run the tests farther down:

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

Please note that this creates a 427.57 MB table in your database and will take something like 15-30 minutes to run. If your database is small and set to 10% growth it will take longer than if you size big enough first.

Now for the actual performance testing script. Please note that it's purposeful to not return rows back to the client as this is crazy expensive on 26 million rows and would hide the performance differences between the methods.

Performance Results

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;

Some Rambling Analysis

Some notes about this. First of all, if just performing a GROUP BY or a comparison, there's no need to convert back to datetime. So you can save some CPU by avoiding that, unless you need the final value for display purposes. You can even GROUP BY the unconverted value and put the conversion only in the SELECT clause:

select Convert(datetime, DateDiff(dd, 0, Tm))
from (select '2010-09-12 00:00:00.003') X (Tm)
group by DateDiff(dd, 0, Tm)

Also, see how the numeric conversions only take slightly more time to convert back to datetime, but the varchar conversion almost doubles? This reveals the portion of the CPU that is devoted to date calculation in the queries. There are parts of the CPU usage that don't involve date calculation, and this appears to be something close to 19875 ms in the above queries. Then the conversion takes some additional amount, so if there are two conversions, that amount is used up approximately twice.

More examination reveals that compared to Convert(, 112), the Convert(, 101) query has some additional CPU expense (since it uses a longer varchar?), because the second conversion back to date doesn't cost as much as the initial conversion to varchar, but with Convert(, 112) it is closer to the same 20000 ms CPU base cost.

Here are those calculations on the CPU time that I used for the above analysis:

     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 is the CPU time for a round trip back to datetime.

• single is CPU time for a single conversion to the alternate data type (the one that has the side effect of removing the time portion).

• base is the calculation of subtracting from single the difference between the two invocations: single - (round - single). It's a ballpark figure that assumes the conversion to and from that data type and datetime is approximately the same in either direction. It appears this assumption is not perfect but is close because the values are all close to 20000 ms with only one exception.

One more interesting thing is that the base cost is nearly equal to the single Convert(date) method (which has to be almost 0 cost, as the server can internally extract the integer day portion right out of the first four bytes of the datetime data type).

Conclusion

So what it looks like is that the single-direction varchar conversion method takes about 1.8 μs and the single-direction DateDiff method takes about 0.18 μs. I'm basing this on the most conservative "base CPU" time in my testing of 18458 ms total for 25,920,000 rows, so 23218 ms / 25920000 = 0.18 μs. The apparent 10x improvement seems like a lot, but it is frankly pretty small until you are dealing with hundreds of thousands of rows (617k rows = 1 second savings).

Even given this small absolute improvement, in my opinion, the DateAdd method wins because it is the best combination of performance and clarity. The answer that requires a "magic number" of 0.50000004 is going to bite someone some day (five zeroes or six???), plus it's harder to understand.

Additional Notes

When I get some time I'm going to change 0.50000004 to '12:00:00.003' and see how it does. It is converted to the same datetime value and I find it much easier to remember.

For those interested, the above tests were run on a server where @@Version returns the following:

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)

这篇关于如何清除datetime值的时间部分（SQL Server）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！