user defined function problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

user defined function problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-09 : 08:30:02

ashish writes "I have written a user defined function in sql server to get the gmt time. Below is the function i have written

CREATE FUNCTION GetGMT (@InDateTime datetime)
RETURNS datetime AS
BEGIN
DECLARE @OutDatetime datetime
SET @OutDatetime = dateadd(n,-150,@InDateTime)
RETURN @OutDatetime
END

I have only one login name for the sql server ie 'sa'. Now when i try to access the function like the following
select GetGMT(getdate())
it does not work
but if i specify select dbo.GetGMT(getdate())
it works.

Is it compulsory to give the full qualified name of the object. The issue is when this application is deployed on the client, the owner can change."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-09 : 08:35:27

Yes, you need to use the 2-part name including the owner. However, UDF's should always be created under dbo. And lastly, a better version of this function would be:

CREATE FUNCTION dbo.GetGMT (@InDateTime datetime)
RETURNS datetime AS
BEGIN
DECLARE @OutDatetime datetime
SET @OutDatetime = dateadd(n,DateDiff(n, GETDATE(), GETUTCDATE()),@InDateTime)
RETURN @OutDatetime
END

Your current function will not work if used outside of your current timezone, nor does it correct for daylight savings time.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-09 : 09:57:06

quote:
Originally posted by robvolk

Yes, you need to use the 2-part name including the owner. However, UDF's should always be created under dbo. And lastly, a better version of this function would be:

CREATE FUNCTION dbo.GetGMT (@InDateTime datetime)
RETURNS datetime AS
BEGIN
DECLARE @OutDatetime datetime
SET @OutDatetime = dateadd(n,DateDiff(n, GETDATE(), GETUTCDATE()),@InDateTime)
RETURN @OutDatetime
END

Your current function will not work if used outside of your current timezone, nor does it correct for daylight savings time.

I don't think you can use non-deterministic functions, like GETDATE() and GETUTCDATE(), in a user-defined function.

You can work around that by creating a view, and using that in the function:

create view dbo.V_CURRENT_TIME
as
select 
	GETDATE		= GETDATE()
	GETUTCDATE	= GETUTCDATE()

I am wondering what ashish is really trying to do. If he just wants the current GMT (UTC) time, there is a SQL Server function for it, GETUTCDATE(). If he wants to convert dates from local to UTC time, that is a different issue. It is fairly simple to convert current data to UTC, but if you have old data from before the last daylight savings time switch, it gets a lot more complicated. It is also more difficult if you are dealing with data from multiple time zones.

CODO ERGO SUM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-09 : 20:03:26

quote:
I don't think you can use non-deterministic functions, like GETDATE() and GETUTCDATE(), in a user-defined function

!@#$%!@#$%^& I ALWAYS forget that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources