In an earlier post I showed some ways to remove the time portion from a date, but the obvious follow on from that is which is fastest. To test I ran both versions on a dataset of 1.9m rows of data.

The DATE datatype returned the records in 15m 35s = 935s

Getting the data using the DATEADD and DATEDIFF technique took 16m 50s = 1020s

In this example using the DATE datatype was 10% faster – which is significant. From this test the best way to remove the time from a datetime in SQL is by using the DATE datatype.

I’m also aware of techniques to remove the time using text functions. DO NOT USE THESE – they are not handled in an optimised way by the SQL engine and will be far slower than the 2 techniques I have documented.