<p>In SQL Server, how do I "floor" a DATETIME to the second/minute/hour/day/year?</p>

<p>Let's say that I have a date of <strong>2008-09-17 12:56:53.430</strong>, then the output of flooring should be:</p>

<ul>
<li>Year:  2008-01-01 00:00:00.000</li>
<li>Month: 2008-09-01 00:00:00.000</li>
<li>Day: 2008-09-17 00:00:00.000</li>
<li>Hour: 2008-09-17 12:00:00.000</li>
<li>Minute: 2008-09-17 12:56:00.000</li>
<li>Second: 2008-09-17 12:56:53.000</li>
</ul>

The key is to use
DATEADD
and
DATEDIFF
along with the appropriate SQL timespan enumeration.
```
```
declare @datetime datetime;
set @datetime = getdate();
select @datetime;
select dateadd(year,datediff(year,0,@datetime),0);
select dateadd(month,datediff(month,0,@datetime),0);
select dateadd(day,datediff(day,0,@datetime),0);
select dateadd(hour,datediff(hour,0,@datetime),0);
select dateadd(minute,datediff(minute,0,@datetime),0);
select dateadd(second,datediff(second,'2000-01-01',@datetime),'2000-01-01');
select dateadd(week,datediff(week,0,@datetime),-1); --Beginning of week is Sunday
select dateadd(week,datediff(week,0,@datetime),0); --Beginning of week is Monday
```
```
Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor.