Date and Time - Date Floor 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 down to the 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_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')
*/


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