Sql-server – How to truncate a datetime in SQL Server

datetimesql-serversql-server-2008truncate

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

Best Solution

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.

Related Question