In an earlier post I showed some ways to remove the time portion from a date. 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. Based on this test using the DATE datatype is the best way to remove the time from a datetime in SQL.
I’m also aware of techniques to remove the time using text functions. DO NOT USE THESE – they are not handled in an optimal way by the SQL engine. They will be slower than the above techniques.