Best approach to remove time part of datetime in SQL Server

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)

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?

Solution 1:

Strictly, method a is the least resource intensive:

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

Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a 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 i.e. CAST(getdate() AS date). Or just use date datatype 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 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)

Solution 2:

In SQL Server 2008, you can use:

CONVERT(DATE, getdate(), 101)

Solution 3:

Of-course this is an old thread but to make it complete.

From SQL 2008 you can use DATE datatype so you can simply do:

SELECT CONVERT(DATE,GETDATE())

Solution 4:

In SQL Server 2008, there is a DATE datetype (also a TIME datatype).

CAST(GetDate() as DATE)

declare @Dt as DATE = GetDate()

Solution 5:

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

...is not a good solution, per the comments below.

I would delete this answer, but I'll leave it here as a counter-example since I think the commenters' explanation of why it's not a good idea is still useful.