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

小开

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

小开

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

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

小开

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

小开

最佳答案

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)

小开

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);