Best approach to remove time part of datetime in S

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

Strictly, method a is the least resource intensive:

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

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
  Example: Why is my CASE expression non-deterministic?
• float relies on internal storage
• it extends to work out first day of month, tomorrow etc by changing "0" base

Edit, Oct 2011

For SQL Server 2008+, you can CAST to date. Or just use date so no time to remove.

Edit, Jan 2012

A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server

Edit, May 2012

Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but generally it will be a bad idea ...

Edit, Sep 2018, for datetime2

DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'

select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)

Answer 2

I think that if you stick strictly with TSQL that this is the fastest way to truncate the time:

 select convert(datetime,convert(int,convert(float,[Modified])))

I found this truncation method to be about 5% faster than the DateAdd method. And this can be easily modified to round to the nearest day like this:

select convert(datetime,ROUND(convert(float,[Modified]),0))

Answer 3

I, personally, almost always use User Defined functions for this if dealing with SQL Server 2005 (or lower version), however, it should be noted that there are specific drawbacks to using UDF's, especially if applying them to WHERE clauses (see below and the comments on this answer for further details). If using SQL Server 2008 (or higher) - see below.

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.

If using a UDF, it should be noted that you should try to avoid using the UDF as part of a WHERE clause in a query as this will greatly hinder performance of the query. The main reason for this is that using a UDF in a WHERE clause renders that clause as non-sargable, which means that SQL Server can no longer use an index with that clause in order to improve the speed of query execution. With reference to my own usage of UDF's, I'll frequently use the "raw" date column within the WHERE clause, but apply the UDF to the SELECTed column. In this way, the UDF is only applied to the filtered result-set and not every row of the table as part of the filter.

Of course, the absolute best approach for this is to use SQL Server 2008 (or higher) and separate out your dates and times, as the SQL Server database engine is then natively providing the individual date and time components, and can efficiently query these independently without the need for a UDF or other mechanism to extract either the date or time part from a composite datetime type.

Answer 4

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 5

See this question:
How can I 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 6

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.