Wayne Sheffield compares the performance of four methods for removing time from a DateTime data type:
Today, we’ll compare 3 other methods to the DATEADD/DATEDIFF method:
- 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.
- Using the DATEADD/DATEDIFF routine.
- Converting the datetime to DATE and back to datetime.
- Converting the datetime to varbinary (which returns just the time), and subtracting that from the datetime value.
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.
Click through for the methods, as well as a performance test to see which is fastest.