如何在 SQL Server 中截断日期时间?
在 SQL Server 2008 中截断日期时间值(如删除小时分和秒)的最佳方法是什么?
What's the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server 2008?
例如:
declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)
-----------------------
2009-05-28 00:00:00.000
推荐答案
即使在几年后，这仍然经常收集更多的选票，因此我需要为现代版本的 Sql Server 更新它.对于 Sql Server 2008 及更高版本，很简单:
This continues to frequently gather additional votes, even several years later, and so I need to update it for modern versions of Sql Server. For Sql Server 2008 and later, it's simple:
cast(getDate() As Date)
请注意，靠近底部的最后三段仍然适用，您通常需要退后一步，首先找到避免演员阵容的方法.
Note that the last three paragraphs near the bottom still apply, and you often need to take a step back and find a way to avoid the cast in the first place.
但是还有其他方法可以实现这一点.以下是最常见的.
But there are other ways to accomplish this, too. Here are the most common.
正确的方法(自 Sql Server 2008 以来的新方法):
cast(getdate() As Date)
正确的方法(旧):
dateadd(dd, datediff(dd,0, getDate()), 0)
这已经比较老了，但仍然值得了解，因为它也可以轻松适应其他时间点，例如一个月、一分钟、一小时或一年的第一时刻.
This is older now, but it's still worth knowing because it can also easily adapt for other time points, like the first moment of the month, minute, hour, or year.
这种正确的方法使用了作为 ansi 标准的一部分并保证可以工作的文档化函数，但它可能会稍微慢一些.它的工作原理是查找从第 0 天到当天有多少天，然后将这些天数加回到第 0 天.无论您的日期时间如何存储，无论您的语言环境如何，它都可以工作.
This correct way uses documented functions that are part of the ansi standard and are guaranteed to work, but it can be somewhat slower. It works by finding how many days there are from day 0 to the current day, and adding that many days back to day 0. It will work no matter how your datetime is stored and no matter what your locale is.
快速方法:
cast(floor(cast(getdate() as float)) as datetime)
这是有效的，因为日期时间列存储为 8 字节二进制值.将它们转换为浮动，将它们打平以删除分数，当您将它们转换回日期时间时，值的时间部分消失了.这一切都只是位移，没有复杂的逻辑，而且非常很快.
This works because datetime columns are stored as 8-byte binary values. Cast them to float, floor them to remove the fraction, and the time portion of the values are gone when you cast them back to datetime. It's all just bit shifting with no complicated logic and it's very fast.
请注意，这取决于 Microsoft 可以随时更改的实施细节，即使在自动服务更新中也是如此.它也不是很便携.在实践中，这个实现不太可能很快改变，但如果你选择使用它，意识到危险仍然很重要.现在我们可以选择将日期投射到约会对象中，这几乎没有必要了.
Be aware this relies on an implementation detail Microsoft is free to change at any time, even in an automatic service update. It's also not very portable. In practice, it's very unlikely this implementation will change any time soon, but it's still important to be aware of the danger if you choose to use it. And now that we have the option to cast as a date, it's rarely necessary.
错误的方式:
cast(convert(char(11), getdate(), 113) as datetime)
错误的方法是转换为字符串，截断字符串，然后转换回日期时间.这是错误的，有两个原因:1)它可能无法在所有语言环境中工作；2)这是最慢的方法来做到这一点……而且不仅仅是一点点；它比其他选项慢一两个数量级.
The wrong way works by converting to a string, truncating the string, and converting back to a datetime. It's wrong, for two reasons: 1)it might not work across all locales and 2) it's about the slowest possible way to do this... and not just a little; it's like an order of magnitude or two slower than the other options.
更新最近获得了一些投票，所以我想补充一点，自从我发布这篇文章以来，我已经看到一些非常确凿的证据表明 Sql Server 将优化消除两者之间的性能差异正确"方式和快速"方式，意味着你现在应该偏向前者.
Update This has been getting some votes lately, and so I want to add to it that since I posted this I've seen some pretty solid evidence that Sql Server will optimize away the performance difference between "correct" way and the "fast" way, meaning you should now favor the former.
在任何一种情况下，您都希望编写查询以避免首先需要这样做.您应该在数据库上进行这项工作的情况非常少见.
In either case, you want to write your queries to avoid the need to do this in the first place. It's very rare that you should do this work on the database.
在大多数地方，数据库已经是你的瓶颈.通常，服务器是为提高性能而添加硬件最昂贵的服务器，也是最难正确添加硬件的服务器(例如，您必须平衡磁盘和内存).从技术和业务角度来看，向外扩展也是最难的.从技术上讲，添加 Web 或应用程序服务器比添加数据库服务器要容易得多，即使这是错误的，您也不必为 IIS 或 apache 的每个服务器许可证支付 20,000 美元以上.
In most places, the database is already your bottleneck. It's generally the server that's the most expensive to add hardware to for performance improvements and the hardest one to get those additions right (you have to balance disks with memory, for example). It's also the hardest to scale outward, both technically and from a business standpoint; it's much easier technically to add a web or application server than a database server and even if that were false you don't pay $20,000+ per server license for IIS or apache.
我想说明的一点是，只要有可能，您都应该在应用程序级别进行这项工作.唯一你应该发现自己在 Sql Server 上截断日期时间的时候是当你需要按天分组时，即使那样你也应该有一个额外的列设置为计算列，维护在插入/更新时间，或在应用程序逻辑中维护.从您的数据库中移除这项破坏索引、占用大量 CPU 的工作.
The point I'm trying to make is that whenever possible you should do this work at the application level. The only time you should ever find yourself truncating a datetime on Sql Server is when you need to group by the day, and even then you should probably have an extra column set up as a computed column, maintained at insert/update time, or maintained in application logic. Get this index-breaking, cpu-heavy work off your database.
相关文章