I just wasted 3 hours trying to figure out how to RETURN the GMT Time Zone of my server in a User Defined function; so I am sharing this workaround.
Due to SQL determinism; If you try to use the GetDate() or GetUTCDate() function within a User Defined Function, you will get an error message:
Error 443: Invalid use of 'getdate' within a function. -or- Error 443: Invalid use of 'getutcdate' within a function.
The WORK AROUND: 1. First, you create VIEW first: CREATE VIEW dbo.SYS_GetServerTimeZone AS SELECT DATEDIFF( hh, GETUTCDATE(), GETDATE() ) AS ServerTimeZone
2. Then, you create a USER DEFINED FUNCTION: CREATE FUNCTION dbo.fniGetServerTimeZone( ) RETURNS INT AS BEGIN DECLARE @viTZ INT SELECT @viTZ = ServerTimeZone FROM SYS_GetServerTimeZone RETURN @viTZ END
PERFORMANCE ISSUE: I am quite sure there is SOME performance issue associated with this. However, at least I get it to work. Comments/Improvement will be appreciated.