Date Ceiling Function

DROP FUNCTION dbo.udf_DateCeiling

GO

CREATE FUNCTION dbo.udf_DateCeiling

(

@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:

-- Add a minute

-- Subtract All Seconds

-- Subtract all Milliseconds

--add one unit of the date part to be rounded

SELECT @seed = CASE

WHEN @part = 'ss' THEN DATEADD(ss,1,@seed)

WHEN @part = 'mi' THEN DATEADD(mi,1,@seed)

WHEN @part = 'hh' THEN DATEADD(hh,1,@seed)

WHEN @part = 'dd' THEN DATEADD(dd,1,@seed)

WHEN @part = 'mm' THEN DATEADD(mm,1,@seed)

WHEN @part = 'yy' THEN DATEADD(yy,1,@seed)

END

-- now 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.dateceiling(getdate(),'ss') union all

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

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

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

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

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

*/