<p>In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'?</p>

<p>Duplicated <a href="https://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server">here</a>.</p>

```
```
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
```
```
DateDiff(Day, 0, GetDate()) is the same as DateDiff(Day, '1900-01-01', GetDate())
Since DateDiff returns an integer, you will get the number of days that have elapsed since Jan 1, 1900.  You then add that integer number of days to Jan 1, 1900.  The net effect is removing the time component.
I should also mention that this method works for any date/time part (like year, quarter, month, day, hour, minute, and second).
```
```
Select  DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)
Select  DateAdd(Quarter, DateDiff(Quarter, 0, GetDate()), 0)
Select  DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
Select  DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
Select  DateAdd(Hour, DateDiff(Hour, 0, GetDate()), 0)
Select  DateAdd(Second, DateDiff(Second, '20000101', GetDate()), '20000101')
```
```
The last one, for seconds, requires special handling.  If you use Jan 1, 1900 you will get an error.
Difference of two datetime columns caused overflow at runtime.
You can circumvent this error by using a different reference date (like Jan 1, 2000).