Date Floor Function

DROP FUNCTION dbo.udf_DateFloor

GO

CREATE FUNCTION dbo.udf_DateFloor

(

@seed DATETIME

, @part VARCHAR(2)

)

RETURNS DATETIME

AS

BEGIN

DECLARE

@second DATETIME,

@minute DATETIME,

@hour DATETIME,

@day DATETIME,

@month DATETIME,

@year DATETIME,

@retDate DATETIME

-- NOTE: at first this looks like this code would be more efficient if the

-- code for each date part were split into IF..THEN..ELSE

-- but it is necessary to perform the central block so that the effect

-- is to subtract all of the smalller date part values from the starting point

-- ie for Minute ceiling:

-- Subtract All Seconds

-- Subtract all Milliseconds

--the only difference from the udf_DateCeiling is that we don't need to add one of the @part units.

-- for each level of date part, subtract all of the units of the next smallest date part

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)

-- and load the return variable with the date part required

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

/*

Sample Usage (uses standard dateparts):

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

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

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

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

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

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

*/