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.