Best approach to remove time part of datetime in SQL Server

Question

Which method provides the best performance when removing the time portion from a datetime field in SQL Server?

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

or

b) select cast(convert(char(11), getdate(), 113) as datetime)

The second method does send a few more bytes either way but that might not be as important as the speed of the conversion.

Both also appear to be very fast, but there might be a difference in speed when dealing with hundreds-of-thousands or more rows?

Also, is it possible that there are even better methods to get rid of the time portion of a datetime in SQL?

Answer 1

Strip time on inserts/updates in the first place. As for on-the-fly conversion, nothing can beat a user-defined function maintanability-wise:

select date_only(dd)

The implementation of date_only can be anything you like - now it's abstracted away and calling code is much much cleaner.

Answer 2

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

Answer 3

Could always use / abuse the casted floating point representation of the date.

Select cast(floor(cast (getdate() as float )) as datetime)

Edit : As pointed out, the internal representation is not as simple as a floating point, but internally, it is 2, 4 byte integers. I've changed my text to say 'casted fp representation' to make that clearer.

Answer 4

I almost always use User Defined functions for this.

In fact, for most databases that I create, I add these UDF's in right near the start since I know there's a 99% chance I'm going to need them sooner or later.

I create one for "date only" & "time only" (although the "date only" one is by far the most used of the two).

Here's some links to a variety of date-related UDF's:

Essential SQL Server Date, Time and DateTime Functions
Get Date Only Function

That last link shows no less than 3 different ways to getting the date only part of a datetime field and mentions some pros and cons of each approach.

Of course, the absolute best approach is to use SQL Server 2008 and separate out your dates and times.

Answer 5

Strictly, method a is the least resource intensive.

Proven less CPU intensive for same total duration a million rows by some one with way too much time on their hands: Most efficient way in SQL Server to get date from date+time?

I saw a similar test elsewhere with similar results too.

I prefer the DATEADD/DATEDIFF because:

• varchar is subject to language/dateformat issues
• float relies on internal storage
• it extends to work out first day of month, tomorrow etc by changing "0" base

Answer 6

See this question:
http://stackoverflow.com/questions/923295/how-to-truncate-a-datetime-in-sql-server

Whatever you do, don't use the string method. That's about the worst way you could do it.

Answer 7

Here's yet another answer, from another duplicate question:

SELECT CAST(CAST(getutcdate() - 0.50000004 AS int) AS datetime)

This magic number method performs slightly faster than the DATEADD method. (It looks like ~10%)

The CPU Time on several rounds of a million records:

DATEADD   MAGIC FLOAT
500       453
453       360
375       375
406       360

But note that these numbers are possibly irrelevant because they are already VERY fast. Unless I had record sets of 100,000 or more, I couldn't even get the CPU Time to read above zero.

Considering the fact that DateAdd is meant for this purpose and is more robust, I'd say use DateAdd.

Answer 8

Already answered but ill throw this out there too... this suposedly also preforms well but it works by throwing away the decimal (which stores time) from the float and returning only whole part (which is date)

 CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)

second time I found this solution... i grabbed this code off