T-SQL – How to Strip Date from Datetime

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)

The reason why your query doesn't return the row you expect, is because GETDATE() returns the date and time portion at the moment the query was executed. The value in your DateCreated column will not match the time portion, so no rows are returned.

There are various ways to construct a query so that it evaluates the date based on only the date component. Here's one example:

WHERE YEAR(datecreated) = YEAR(GETDATE())
  AND MONTH(datecreated) = MONTH(GETDATE())
  AND DAY(datecreated) = DAY(GETDATE())

The unfortunate reality is that any query using a function on the column means that if an index exists on the column, it can't be used.