Best approach to remove time part of datetime in SQL Server

In SQL Server 2008, you can use:

CONVERT(DATE, getdate(), 101)

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())

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)

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

CAST(GetDate() as DATE)

or

declare @Dt as DATE = GetDate()