Best Approach to Remove Time Part of Datetime in SQL Server

Best approach to remove time part of datetime in SQL Server

Strictly, method a is the least resource intensive:

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time?

I saw a similar test elsewhere with similar results too.

I prefer the DATEADD/DATEDIFF because:

• varchar is subject to language/dateformat issues
  
  Example: Why is my CASE expression non-deterministic?
• float relies on internal storage
• it extends to work out first day of month, tomorrow, etc by changing "0" base

Edit, Oct 2011

For SQL Server 2008+, you can CAST to date i.e. CAST(getdate() AS date). Or just use date datatype so no time to remove.

Edit, Jan 2012

A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server

Edit, May 2012

Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here Common SQL Programming Mistakes

Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but generally it will be a bad idea ...

Edit, Sep 2018, for datetime2

DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'

select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)

How to remove the time portion of a datetime value (SQL Server)?

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 In SQL Server 2005 and Older?

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)

Remove Time from DateTime values returned in Query

SELECT convert(char(10), DataLog.TimestampUTC, 120) as TimestampUTC, 
MeterTags.Name,DataLog.Data
FROM DataLog
INNER JOIN MeterTags
ON DataLog.MeterTagId = MeterTags.MeterTagId
WHERE DataLog.TimeStampUTC between cast(getdate() - 1 as date) and cast(getdate() as date) and
      DataLog.MeterTagId Between 416 AND 462;

Remove timestamp from date value in SQL

Convert to a date:

ALTER TABLE random_table_1 ADD effective_end_date AS
     CONVERT(date, COALESCE(End_dt, GETDATE())) ;

Remove time from DateTime sql server 2005

SELECT CONVERT(VARCHAR(10),GETDATE(),105)

How can I truncate a datetime in SQL Server?

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 Topics