intellectually constipated

patrick gallucci's technology brain drain

SQL Server Function to Return Date from Numeric Value

SQL Server Function to Return Date from Numeric Value

Rate This
  • Comments 1

This function will return a datetime for a numeric value. 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: IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetDateFromNumeric]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
   2: DROP FUNCTION [util].[uf_GetDateFromNumeric]
   3: GO
   4:  
   5: CREATE FUNCTION [util].[uf_GetDateFromNumeric](
   6:      @date            NUMERIC(18,10))
   7: RETURNS DATETIME
   8: WITH EXECUTE AS CALLER
   9: AS
  10: /**********************************************************************************************************
  11: * UDF Name:        
  12: *        [util].[uf_GetDateFromNumeric]
  13: * Parameters:  
  14: *         @numeric            datetime
  15: *
  16: * Purpose: This function returns a datetime for a numeric value. This is 
  17: *    useful when you need to convert a key value into a datetime.
  18: *
  19: * Example:
  20:     select util.uf_GetDateFromNumeric(10)
  21: *              
  22: * Revision Date/Time:
  23: *    July 31, 2007
  24: *
  25: **********************************************************************************************************/
  26: BEGIN
  27:     DECLARE @result DATETIME
  28:  
  29:     --CHECK FOR NULL DATE
  30:     IF (@date is null)
  31:         SET @result = CAST(0 AS DATETIME)
  32:     ELSE BEGIN
  33:         SET @result = CAST(@date AS DATETIME)
  34:     END --if
  35:  
  36:     --Return result
  37:     RETURN @result
  38: END
  39: GO
  40:  
  41: select util.uf_GetDateFromNumeric(NULL)
  42: select util.uf_GetDateFromNumeric(10)
Comments
Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post