Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0

share|improve this question
up vote 72 down vote accepted

The fastest if you have to iterate over a recordset and don't have date in SQL Server 2008

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

Two different and excellent answers on StackOverflow bear this out: One, Two

Varchar conversions are one of the worst ways to do it. Of course, for one value it may not matter but it's a good habit to get into.

This way is also deterministic, say if you want to index a computed column. Even folk who write books about SQL Server get caught out with datetime conversions

This technique is also extendable.

  • yesterday: DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
  • start of month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
  • end of last month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
  • start of next month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

Edit:

As I mentioned about determinism, varchar methods are not safe unless you use style 112.

Other answers here point out that you'd never apply this to a column. This is correct, but you may want to select a 100k rows or add a computed column or GROUP BY dateonly. Then you have to use this method.

The other answer also mentions style 110. This is not language or SET DATEFORMAT safe and fails with "british" language setting. See the ultimate guide to the datetime datatypes by Tibor Karaszi.

share|improve this answer

You have to convert it to varchar specifying a format pattern (110 in this case) then convert (or cast) back to datetime.

select getdate(), cast(convert(varchar(10), getdate(), 110) as datetime)
share|improve this answer

protected by gbn Jul 31 '13 at 6:35

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.