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