Removing the time element from a datetime

Killing Time – or removing the time element from a datetime

Over the course of time, we all collect scripts and routines for helping us do our job. Recently, I ran across two routines that are removing the time element from a datetime data type that I hadn’t seen before. A quick initial test showed that these seem to perform pretty well… but how well do they perform compared to the other ways of doing this? It’s time to find out.

I have blogged before about working with dates, where I checked out how the DATETIMEFROMPARTS function performs compared to the DATEADD/DATEDIFF functions. Today, we’ll compare 3 other methods to the DATEADD/DATEDIFF method:

1. Taking advantage of the fact that a datetime datatype is stored as a float, with the decimal being fractions of a day and the whole numbers being days, we will convert the datetime to float, taking the floor (just the whole numbers), and converting back to datetime.
2. Using the DATEADD/DATEDIFF routine.
3. Converting the datetime to DATE and back to datetime.
4. Converting the datetime to varbinary (which returns just the time), and subtracting that from the datetime value.

Update: added tests for converting the datetime to integer / bigint and back to datetime after Eirikur’s comment below.

While there are other ways of stripping the time (DATETIMEFROMPARTS, string manipulation), those ways are already known as poorly performing. Let’s just concentrate on these four.

Four Six methods of removing the time element from a datetime

First off, we need to ensure that we have the calculations down correctly. This query uses the four methods to strip the time element from the datetime value:

This returns the following result set, in which we can see that all the calculations are removing the time element:

The one million row performance test

Now, it’s time to test how these methods perform. As you may have gathered from my earlier tests, a true test requires a lot of data. In this test, I will create a one million row dataset of dates, and then strip the time from them. The results are sent into a temporary table so that the test is not affected by the display time of the millions of rows. I’ll store off the start/end time for processing that method. The test runs over 500 iterations for each method, and at the end I’ll get the MIN, MAX and AVG elapsed time (in microseconds) for each method.

The test that I’ll use is:

Over 500 iterations, the results are:

This result is sorted by the average time. The first thing to note is that all four of these methods are very fast. There is only 63 milli-seconds difference between the best and worse methods (on average).

In Summary

SQL 2008 introduced the DATE data type. I have tried to not use the convert to DATE method because the DATE datatype wasn’t in all versions. It sucks to still be on SQL 2005. Since this method has the best times (on average), I think I’ll be using this in the future. But what was really surprising was that the two methods that I hadn’t seen before performed better than I expected, and better than the method that I had used.

Update

After Eirikur’s comment below, I added a test (and updated the code above) for converting to integer and to bigint, and reran the test. Updated results:

The conversion to DATE is still the fastest, but converting to bigint and back to datetime is now the fastest way in 2005.