Compartir a través de

SQL Server Function to Return Numeric Value for DateTime

  • Artículo

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

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 https://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

  • Anonymous
    July 31, 2007
    Technorati Tags: SQL Server , SQL , Function , Intellectually Constipated This function will return a

  • Anonymous
    September 20, 2011
    I want to extract yyyymm format from datetime and store it as numeric. Please suggest..