This is not the best way to implement this -- See http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server

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

or converted to this function:

create function dbo.DATEFLOOR (

@seed datetime

, @part varchar(2)

)

returns datetime

as

begin

/*

Sample Usage (uses standard dateparts):

select 'second', dbo.datefloor(getdate(),'ss') union all

select 'minute', dbo.datefloor(getdate(),'mi') union all

select 'hour' , dbo.datefloor(getdate(),'hh') union all

select 'day' , dbo.datefloor(getdate(),'dd') union all

select 'month' , dbo.datefloor(getdate(),'mm') union all

select 'year' , dbo.datefloor(getdate(),'yy')

*/

select @retDate = case

when @part = 'ss' then dateadd(second,datediff(second,'2000-01-01',@seed),'2000-01-01')

when @part = 'mi' then dateadd(minute,datediff(minute,0,@seed),0)

when @part = 'hh' then dateadd(hour,datediff(hour,0,@seed),0)

when @part = 'dd' then dateadd(day,datediff(day,0,@seed),0)

when @part = 'mm' then dateadd(month,datediff(month,0,@seed),0)

when @part = 'yy' then dateadd(year,datediff(year,0,@seed),0)

end

return @retDate

end