Date and Time - Date Ceiling Function

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        29 Nov 2010
 

Description

This is a function to round a DATETIME value up to the next whole value for a specified DATE PART.
 
Supported DATE PARTS are: 
 ss - second
 mi - minute
 hh - hour
 dd - day
 mm - month
 yy - year
 

Code

 

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

 
ċ
udf_DATECEILING.sql
(2k)
Andy Hughes,
Jun 23, 2012, 1:52 AM
Comments