How do I get just the date when using MSSQL GetDate()?

DELETE from Table WHERE Date > GETDATE();

GETDATE() includes time. Instead of getting

2011-01-26 14:58:21.637

How can I get:

2011-01-26 00:00:00.000

Slight bias to SQL Server

• Best approach to remove time part of datetime in SQL Server
• Most efficient way in SQL Server to get date from date+time?

Summary

DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

SQL Server 2008 has date type though. So just use

CAST(GETDATE() AS DATE)

Edit: To add one day, compare to the day before "zero"

DATEADD(day, DATEDIFF(day, -1, GETDATE()), 0)

From cyberkiwi:

An alternative that does not involve 2 functions is (the +1 can be in or ourside the brackets).

DATEDIFF(DAY, 0, GETDATE() +1)

DateDiff returns a number but for all purposes this will work as a date wherever you intend to use this expression, except converting it to VARCHAR directly - in which case you would have used the CONVERT approach directly on GETDATE(), e.g.

convert(varchar, GETDATE() +1, 102)

For SQL Server 2008, the best and index friendly way is

DELETE from Table WHERE Date > CAST(GETDATE() as DATE);

For prior SQL Server versions, date maths will work faster than a convert to varchar. Even converting to varchar can give you the wrong result, because of regional settings.

DELETE from Table WHERE Date > DATEDIFF(d, 0, GETDATE());

_{Note: it is unnecessary to wrap the DATEDIFF with another DATEADD}

It's database specific. You haven't specified what database engine you are using.

e.g. in PostgreSQL you do cast(myvalue as date).

SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101))

You can use

DELETE from Table WHERE Date > CONVERT(VARCHAR, GETDATE(), 101);

CONVERT(varchar,GETDATE(),102)

Here you have few solutions ;)

http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm