Global System UDF to return ISO Date string

,

Updated for Permission assignment change 1/11/1006.

Ok, I'll bet that you are asking, What is a Global System UDF, right? By Global I mean that the UDF is available from any database on the server. By System I mean that the two part naming rule does not apply to this scalar function.

The UDF conversion from datetime to an ISO format Date string is not hard, but something I use every day.

No need to convert this char(8) return value to a datetime datatype as T-SQL will make the conversion for us, plus the ISO date format is independent of the DATEFORMAT setting.

Enjoy,

Andy

-- Create global system function fn_DateTimeToISODate - 6/10/2005
--
-- Be aware that a service pack could remove this
-- non-Microsoft object from the master database
--
-- Best Practices Analyzer Tool for Microsoft SQL Server 
-- Rule: User Objects in Master - will report Non Compliance
--
-- In master database the function is global
-- or avaiable from any database
USE master
GO
-- Drop fn_DateTimeToISODate function, system or not
IF EXISTS(SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE ROUTINE_TYPE=N'FUNCTION' AND ROUTINE_SCHEMA=N'system_function_schema' 
		AND ROUTINE_NAME = N'fn_DateTimeToISODate')
	BEGIN
		PRINT 'Drop function owned by system_function_schema'
		EXEC sp_configure 'allow updates', 1
		RECONFIGURE WITH OVERRIDE
		DROP FUNCTION system_function_schema.fn_DateTimeToISODate
		EXEC sp_configure 'allow updates', 0
		RECONFIGURE WITH OVERRIDE
		PRINT ''
	END
ELSE
	IF EXISTS(SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
		WHERE ROUTINE_TYPE=N'FUNCTION' AND ROUTINE_SCHEMA=N'dbo' 
			AND ROUTINE_NAME = N'fn_DateTimeToISODate')
		BEGIN
			PRINT 'Drop function owned by dbo'
			DROP FUNCTION dbo.fn_DateTimeToISODate
			PRINT ''
		END
GO
CREATE FUNCTION fn_DateTimeToISODate 
(
	@Date_Time datetime
)
RETURNS char(8)
AS
BEGIN
	DECLARE	@Date char(8)
	-- Removing the time portion of a datetime value
	-- Return the ISO Date formated yyyymmdd string
	SELECT @Date = CONVERT(varchar, @Date_Time,112)
	RETURN @Date
END
GO

-- This will make the a scalar user-defined function a system function
-- or the two-part name invoking rule no longer is required
PRINT 'Change owner'
EXEC sp_changeobjectowner 'fn_DateTimeToISODate', 'system_function_schema'
GO

PRINT 'Grant rights'
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GRANT EXEC ON system_function_schema.fn_GreatCircleDistance TO public AS system_function_schema
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

PRINT 'Usage:'
PRINT 'SELECT fn_DateTimeToISODate(GETDATE())'
PRINT ''
PRINT 'Finished creating fn_DateTimeToISODate'
GO

Rate

5 (1)

Share

Share

Rate

5 (1)