intellectually constipated

patrick gallucci's technology brain drain

SQL Server Function to Return Numeric Value for DateTime

SQL Server Function to Return Numeric Value for DateTime

Rate This
  • Comments 2

This function will return a numeric value for a given date. This is using the 1900 date system, the same as used by Excel. See http://office.microsoft.com/en-us/excel/HP100791811033.aspx for more examples.

 

   1:  
   2: IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetNumericDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
   3: DROP FUNCTION [util].[uf_GetNumericDate]
   4: GO
   5:  
   6: CREATE FUNCTION [util].[uf_GetNumericDate](
   7:      @date            DATETIME = null)
   8: RETURNS NUMERIC(18,10)
   9: WITH EXECUTE AS CALLER
  10: AS
  11: /**********************************************************************************************************
  12: * UDF Name:        
  13: *        [util].[uf_GetNumericDate]
  14: * Parameters:  
  15: *         @date            datetime
  16: *        ,@seperator        varchar(1) = null)
  17: *
  18: * Purpose: This function returns a @date as numeric format. This is 
  19: *    useful when you need a key value instead of a datetime.
  20: *
  21: * Example:
  22:     select util.uf_GetNumericDate(getdate())
  23: *              
  24: * Revision Date/Time:
  25: *    July 31, 2007
  26: *
  27: **********************************************************************************************************/
  28: BEGIN
  29:     DECLARE @result NUMERIC(18,10)
  30:  
  31:     --CHECK FOR NULL DATE
  32:     IF (@date is null)
  33:         SET @result = CAST(GETDATE() AS NUMERIC(18,10))
  34:     ELSE BEGIN
  35:         SET @result = CAST(@date AS NUMERIC(18,10))
  36:     END --if
  37:  
  38:     --Return result
  39:     RETURN @result
  40: END
  41: GO
  42:  
  43: select util.uf_GetNumericDate(getdate())
Comments
  • Technorati Tags: SQL Server , SQL , Function , Intellectually Constipated This function will return a

  • I want to extract yyyymm format from datetime and store it as numeric.

    Please suggest..

Page 1 of 1 (2 items)
Leave a Comment
  • Please add 8 and 3 and type the answer here:
  • Post