86
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
Md. Zakir Hossain
  • 1,000
  • 9
  • 21
sooprise
  • 21,789
  • 65
  • 185
  • 266

7 Answers7

89

Slight bias to SQL Server

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)
Community
  • 1
  • 1
gbn
  • 408,740
  • 77
  • 567
  • 659
11

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

RichardTheKiwi
  • 102,799
  • 24
  • 193
  • 261
  • The "older" style without DATEADD relies on implicit conversion from int to date based on [precedence](http://msdn.microsoft.com/en-us/library/ms190309.aspx). This is effectively DATEADD in practice, no? I prefer explicit conversions myself, even though this looks cleaner. – gbn Jan 27 '11 at 20:45
  • Not sure how QO goes about it, but there is no reason to manually request a DATEADD. Even though the datatype is datetime, `internally` when comparing, it is converted to a numeric value, so it may be better to just leave it just as an int. – RichardTheKiwi Jan 27 '11 at 21:07
7

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

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

RichardTheKiwi
  • 102,799
  • 24
  • 193
  • 261
FolksLord
  • 962
  • 2
  • 9
  • 16
5
SELECT CONVERT(DATETIME, CONVERT(varchar(10), GETDATE(), 101))
Gabriele Petrioli
  • 183,160
  • 33
  • 252
  • 304
JohnOpincar
  • 5,240
  • 3
  • 31
  • 34
  • It determines the format of the output string, in this case mm/dd/yyyy hh:mm:ss. Hence the varchar(10) which truncates the time portion. – JohnOpincar Jan 26 '11 at 21:46
0

You can use 

DELETE from Table WHERE Date > CONVERT(VARCHAR, GETDATE(), 101);
Soner Gönül
  • 94,086
  • 102
  • 195
  • 339
Shafeeq Koorimannil
  • 724
  • 5
  • 14
  • That returns a string. Best not to rely on implicit conversion. Instead, cast it to a date or date/time explicitly. – Leigh Mar 01 '17 at 22:36
-1
CONVERT(varchar,GETDATE(),102)
Saggio
  • 2,192
  • 6
  • 32
  • 49
  • The 101, 102, etc, are just parameters for the CONVERT function. I believe you might actually want to use 101 which is in the form of mm/dd/yyyy, while 102 is in the form mm.dd.yyyy I believe... – Saggio Jan 26 '11 at 21:06
  • That returns a string. Instead of relying on implicit conversion, best to cast it to a date explicitly. – Leigh Mar 01 '17 at 22:35
-1

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

xx77aBs
  • 4,588
  • 8
  • 49
  • 74
  • Sorry, but that link is incomplete: it doesn't consider the dateadd/datediff technique – gbn Jan 26 '11 at 21:20