SQL Server Function to Return Date from Numeric Value

This function will return a datetime for a numeric value. This is using the 1900 date system, the same as used by Excel. See https://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)

Technorati Tags: SQL Server, SQL, Intellectually Constipated, Function, DateTime