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