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
This question is related to
sql
sql-server
tsql
date
datetime
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))
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
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
You can use
DELETE from Table WHERE Date > CONVERT(VARCHAR, GETDATE(), 101);
Source: Stackoverflow.com