如何在SQL Server中截断datetime？ [英] How can I truncate a datetime in SQL Server?
问题描述
例如：
$ b在SQL Server 2008中截断日期时间值（删除小时数和秒）的最佳方法是什么？$ b
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和更高版本，它很简单：
cast（getDate（）As Date）
请注意，底部附近的最后三个段落仍然适用，您经常需要退后一步，找到避免演员的方法首先，
但是还有其他方法可以实现这一点。这是最常见的。
正确的方法（Sql Server 2008以来的新功能）：
cast（getdate（）As Date）
正确的方式（旧）：
dateadd（dd，datediff（dd，0，getDate ）），0）
这个比较老了，但还是值得一提，因为它也可以很容易地适应对于其他时间点，像月份，分钟，小时或年份的第一时间。
这种正确的方式使用作为ansi标准一部分的文档化函数，保证工作，但可以稍慢一些。它可以通过查找从第0天到当天的天数，并将这些天添加到第0天。无论您的日期时间如何存储，无论您的区域是什么，它都可以工作。
快速的方式：
cast（floor（cast （getdate（）作为float））作为datetime）
这是因为datetime列存储为8-字节二进制值。将它们放置成浮动，将它们放置以删除分数，并且当您将它们转换为datetime时，值的时间部分将消失。这一切都只是一点点移动，没有复杂的逻辑，它是非常快。
请注意，这依赖于实现细节，即使在自动服务更新中，Microsoft也可以随时更改。它也不是很便携。实际上，这个实施很快就不太可能会改变，但是如果你选择使用它，这一点很重要。现在我们可以选择作为日期来投放，这是很少必要的。
错误的方式：
转换（转换（char（11），getdate（），113）作为datetime）
错误的方法通过转换为字符串，截断字符串，并转换回datetime。这是错误的，有两个原因：1）它可能无法在所有地区使用，2）它是最慢的方法来做到这一点，而不只是一点点;它比其他选项要慢一个数量级或两倍。
更新最近有一些投票，所以我想添加到自从我发布这个以来，我看到一些非常实在的证据表明，Sql Server将优化正确方式与快速方式之间的性能差异，这意味着你现在应该赞成前者。
在这两种情况下，您希望编写您的查询，以避免首先需要这样做。在数据库上应该做这个工作是非常罕见的。
在大多数地方，数据库已经是您的瓶颈。通常，服务器是增加硬件以提高性能的最昂贵的服务器，而最难以获得正确添加的服务器（例如，您必须平衡磁盘与内存）。在技术上和从商业角度来看，也是最难扩展的。添加网络或应用程序服务器比数据库服务器要容易得多，即使是错误的，您也不会为IIS或apache支付$ 20,000 +的服务器许可证。
我想要做的是，只要有可能，您应该在应用程序级别进行此工作。您应该发现自己在SQL Server上截断日期时间的时间是当您需要在一天内进行分组，即使这样，您应该可以将额外的列设置为计算列，维护在插入/更新时间，或维护在应用程序逻辑中。从数据库中获取这个索引破解，重量级的工作。
What's the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server 2008?
For example:
declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)
-----------------------
2009-05-28 00:00:00.000
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.
The correct way (new since Sql Server 2008):
cast(getdate() As Date)
The correct way (old):
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.
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.
The fast way:
cast(floor(cast(getdate() as float)) as datetime)
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.
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.
The wrong way:
cast(convert(char(11), getdate(), 113) as datetime)
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.
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.
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.
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.
这篇关于如何在SQL Server中截断datetime？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
