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

64

This question already has an answer here:

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
sooprise

출처 sql sql-server tsql date datetime
  • Also, consider that you might want >= rather than >, or you'll actually miss out on deleting records that are exactly 2011-01-26 00:00:00.000... - Matt Gibson
  • Good call, I actually changed that before reading this comment :) - sooprise
  • A simple solution would be cast(left(getdate(), 11) as datetime) - Mohammad Anini

7 답변

70

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)

  • How do I add one day to this value? - sooprise
  • select CAST(GETDATE() + 1 AS DATE) - eidgenossen
  • @eidgenossen: Simple, but relies on an implicit conversion. Less clear. - gbn
9

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

  • The "older" style without DATEADD relies on implicit conversion from int to date based on precedence. This is effectively DATEADD in practice, no? I prefer explicit conversions myself, even though this looks cleaner. - gbn
  • 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
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).

5
 

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

  • What is the 101 for? - sooprise
  • 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
0

You can use 

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

  • That returns a string. Best not to rely on implicit conversion. Instead, cast it to a date or date/time explicitly. - Leigh
-1
 

CONVERT(varchar,GETDATE(),102)

  • What is the 102 for? - sooprise
  • 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
  • That returns a string. Instead of relying on implicit conversion, best to cast it to a date explicitly. - Leigh
-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

  • Sorry, but that link is incomplete: it doesn't consider the dateadd/datediff technique - gbn

Linked

Related

Latest