How can I truncate a datetime in SQL Server?

Question

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

Solution 1

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.

Solution 2

For SQL Server 2008 only

CAST(@SomeDateTime AS Date) 

Then cast it back to datetime if you want

CAST(CAST(@SomeDateTime AS Date) As datetime)

Solution 3

Just for the sake of a more complete answer, here's a working way for truncating to any of the date parts down and including minutes (replace GETDATE() with the date to truncate).

This is different from the accepted answer in that you can use not only dd (days), but any of the date parts (see here):

dateadd(minute, datediff(minute, 0, GETDATE()), 0)

Note that in the expression above, the 0 is a constant date on the beginning of a year (1900-01-01). If you need to truncate to smaller parts, such as to seconds or milliseconds, you need to take a constant date which is closer to the date to be truncated to avoid an overflow.

Solution 4

The snippet I found on the web when I had to do this was:

 dateadd(dd,0, datediff(dd,0, YOURDATE))
 e.g.
 dateadd(dd,0, datediff(dd,0, getDate()))

Solution 5

CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)

View more solutions

Comments

[KM.]

I'm on 2005, but I thought 2008 had some new function for this??

[flytzen]

Neat! I would have resorted to splitting out the dateparts and using string handling to putting them back together. May not be relevant, but SQL2008 has a pure date-only data type without a time element.

[Joel Coehoorn]

Good point: I'm still on 2005 and so for 2008 this is probably the new "correct" way and may even match the performance of the "fast" way.

[ErikE]

The performance of this new way is even faster than the "fast" way.

[ErikE]

Joel, your update to this post is useful. What's strange is that I found the DateDiff method to be faster, at least in SQL 2008. I'll try running my tests in SQL 2000 and will see if I can post an update.

[Sam Saffron]

the "fast way" is still the fastest way for sql 2008 according to a benchmark I just ran

[Lucero]

Be careful with rounding errors due to floating point precision limits though... also, this doesn't work with the datetime2 data type.

[ZygD]

FYI: stackoverflow.com/q/1177449/27535 and stackoverflow.com/q/133081/27535 The dateadd/datediff "wins...". For a single variable, who cares of course, and one hopes that you have computed columns or such over a million rows :-)

[Kelly Cline]

This "correct" way only accidentally works. The way it is written is as if the syntax for DateAdd were (interval, date, increment), but it isn't. It is (interval, increment, date). I stumbled on this when I tried to truncate a date to the first of the month: SELECT DATEADD( m, 0, DATEDIFF( m, 0, GETDATE( ) ) ) does not work, but SELECT DATEADD( m, DATEDIFF( m, 0, GETDATE( ) ), 0 ) does. At least, this is what I see in 2008R2.

[Kelly Cline]

For Hour, SELECT DATEADD( hour, DATEDIFF( hour, 0, GETDATE( ) ), 0 ) works, also. Minute, too, but Second will result in an overflow.

[Joel Coehoorn]

@Kelly in 2008R2, why not just cast(getdate() as date)?

[Chris Doyle]

This looks like a solution for Oracle, not SQL Server.

[Michael]

This was monstrously helpful. I looked all over for a way to truncate the date-time at a place lower than the full day.

[Lucero]

@Michael, thanks for the feedback, good to know that it helped you out!

[ErikE]

And note you have the DateAdd operands mixed up, it's DateAdd(dd, DateDiff(...), 0). This can bite you if you're not careful.

[ErikE]

Casting to float and back to datetime doesn't work correctly.

[ErikE]

Casting to float and back to datetime doesn't work correctly.

[jtate]

+1 this should have more upvotes, it's a great answer that expands on the selected answer.

[Sam]

I ran into substantial performance degradation when using the CAST method in SQL Server 2008 R2. I was using it in a query that covered ~8.5m rows, and it took at least half an hour (before I terminating the query). Removing the CAST method and using the "old correct way" made the query instant.

[Rosdi Kasim]

'The fast way' work on datetime column as well and not just on getdate() function. For example: This CAST(FLOOR(CAST(CreatedDate AS float)) AS datetime) AS CreatedDate work.., where CreatedDate is a column in your table.

[Joel Coehoorn]

They all work on a datetime column. getdate() here is a stand-in for whatever datetime source you may have.

[NickG]

Re: Get this cpu-heavy work off your database. Is it really THAT much work though? I mean my telephone can render a very complex 3D environment with millions of texture mapped triangles 60 times a second. Is removing the time from a date in SQL Server really going to cause a problem you could even measure (even for millions of rows)?

[NickG]

Regarding my above comment: I just did some tests. All four methods take exactly the same amount of time to run to within 2%. I tested on a million iterations and it took 3300ms in each instance, to within 20ms. Even the "slow" way is just as fast as the other methods (SQL Server 2012). Removing the cast completely as a control saves about 30% (ie, SQL Server seems to spend 2 seconds in 1M iterations of an empty while loop - which surprised me!).

[Joel Coehoorn]

Your telephone is only serving one user. Your DB needs to handle potentially thousands of users simultaneously, with each user running several queries and each query needing potentially thousands to millions of calculations. Yes, that CPU difference can definitely matter. For the testing... these things can change version to version, but you also need to be very sure you're running fair tests. Sql Server does a lot of things with caching, indexing, etc that can invalidate simple tests.

[Shannon Severance]

A couple of notes: 1) DateDiff/DateAdd from zero can also be used to at other time intervals besides day, DATEADD(MONTH, datediff(MONTH,0, GETDATE()), 0). 2) The fast way cast to float -> floor -> cast to datetime does not work for datetime2 unless additional casts to/from datetime are added.

[Michael]

Just so the internet knows, you don't have to be restricted to full datepart periods. Here's an example for 15 minute intervals, using integer division: dateadd(minute, datediff(minute, 0, GETDATE()) / 15 * 15, 0)

[Christian13467]

I like your solution "the correct way (old)" because it works in many many other usecases.