Global System UDF to return ISO Date string

-- 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