Removing Time From A Datetime Value
January 13, 2011 23 Comments
How much time would a DateTime have if a DateTime had no time?
Ok, forgive the bad pun. But the point remains is a valid one. How can we efficiently strip off the time portion of a DateTime value in SQL Server?
I’ve seen a several methods proffered on the Internet. Some are quite clever; others are not so clever. The latter includes converting the DateTime to a VarChar and using string manipulation techniques to loop off the time. That’s not going to perform well.
So what is the best way? Let’s find out.
I’m going to narrow down the field to only three methods for manageability. There may be other methods out there. If you know of one that’s not covered here and you think may be better, please let me know.
To test each case, I’ll employ the conversion 10,000,000 times to see which finishes the quickest. I’ll do this with the following code.
SET NOCOUNT ON DECLARE @cnt INT = 1; DECLARE @start DATETIME = GETDATE(); DECLARE @var DATETIME; WHILE @cnt < 10000000 BEGIN SET @var = [time removal option] SET @cnt += 1; END SELECT @var; PRINT CAST(DATEDIFF(millisecond, @start, GETDATE()) AS INT);
Cast To A Float And Back
First let’s look at a technique that casts the DateTime to a float, gets the floor of the resulting value, and then casts it back as a DateTime.
CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME);
This option finished in a 16,256 milliseconds.
Adding A Date
The next option uses the DATEDIFF function to calculate the number of days since the beginning of time and then uses the DATEADD function to convert it back to a DateTime value.
DATEADD(dd,0, DATEDIFF(dd,0, GETDATE()));
This completed in 15,593 milliseconds, slightly better then the first option.
Converting To A Char And Back
Finally, let’s consider a technique that converts the DateTime to a Char of a specified format and then casts it back to a DateTime.
CAST(CONVERT(CHAR(11), GETDATE(), 113) AS DATETIME
As you may have expected, the conversion to a Char significantly slowed the process down. In fact coming it at 32,698 milliseconds, it took over twice as long as the second method.
Say, Do You Have The Time?
From the three methods I’ve considered, the second method which included using a DATEDIFF to count the number of days since the beginning of time slightly edged out the first method. Both significantly beat converting the DateTime to a Char string.
So, do you have a favorite method? If so, use the test script I’ve posted about and see how it performs. Let me know how it does.