Binaryworld - DATEFLOOR : Rounding dates to nearest day,hour,minute,second ... [ SQL Server -> T-SQL ]

This function takes a date and a date part parameter and rounds the date down to the nearest date part.

Credit goes to "Gordon Klundt"

Example:
In order to round '2010-11-03 17:44:10.117' to the nearest hour ('2010-11-03 17:00:00.000')

Click here to copy the following block

select dbo.datefloor(cast('2010-11-03 17:44:10.117' as datetime),'hh')

Click here to copy the following block

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')
*/

declare @second datetime
declare @minute datetime
declare @hour datetime
declare @day datetime
declare @month datetime
declare @year datetime
declare @retDate datetime

select @second = dateadd(ms,-1*datepart(ms,@seed), @seed)
select @minute = dateadd(ss,-1*datepart(ss,@second), @second)
select @hour = dateadd(mi,-1*datepart(mi,@minute), @minute)
select @day = dateadd(hh,-1*datepart(hh,@hour), @hour)
select @month = dateadd(dd,-1*(day(@day)-1), @day)
select @year = dateadd(mm,-1*(month(@month)-1), @month)

select @retDate = case
when @part = 'ss' then @second
when @part = 'mi' then @minute
when @part = 'hh' then @hour
when @part = 'dd' then @day
when @part = 'mm' then @month
when @part = 'yy' then @year
end

return @retDate
end

go