sooprise sooprise - 1 year ago 74
SQL Question

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

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

gbn gbn
Answer Source

Slight bias to SQL Server


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

SQL Server 2008 has date type though. So just use


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