How to get only date when using MSSQL GetDate ()?

Question

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 

Answer 1

Slight bias to SQL Server

• Best approach to remove datetime time part in SQL Server
• The most efficient way in SQL Server to get dates from date + time?

Summary

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

SQL Server 2008 is of type date . Therefore just use

 CAST(GETDATE() AS DATE) 

Edit: add one day, compare with day to "zero"

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

From cyberkiwi:

An alternative that does not include 2 functions is (+1 may be in brackets or on our sides).

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

DateDiff returns a number, but for all purposes it will work as the date where you intend to use this expression, except for converting it directly to VARCHAR - in this case you would use the CONVERT approach directly in GETDATE (), for example,

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

Answer 2

For SQL Server 2008, the best and easiest way to index is

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

For previous versions of SQL Server, date math will work faster than converting to varchar. Even conversion to varchar may give an incorrect result due to regional settings.

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

_{Note: there is no need to wrap a DATEDIFF another DATEADD}

Answer 3

This database is specific. You did not indicate which database engine you are using.

eg. in PostgreSQL, you throw (myvalue as date).

Answer 4

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

Answer 5

you can use

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

Answer 6

 CONVERT(varchar,GETDATE(),102) 

Answer 7

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