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.

Three Methods

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, 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.

Filed under: Optimization, SQLServerPedia Syndication, T-SQL