SQL Server Function to Return Numeric Value for DateTime
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 aAnonymous
September 20, 2011
I want to extract yyyymm format from datetime and store it as numeric. Please suggest..