Tuesday, February 14, 2006

User Definded Functions And GETDATE() (nondeterministic built-in functions )

I have read on Louis Davidson's blog a post about UDF and being able to use GETDATE() in a UDF in SQL server 2005. I wasn't aware of that so I would like to bring it to your attention
In SQL server 2005 you are able to use the following nondeterministic built-in functions in Transact-SQL user-defined functions:
CURRENT_TIMESTAMP
GET_TRANSMISSION_STATUS
GETDATE
GETUTCDATE
@@PACK_SENT
@@PACK_RECEIVED
@@MAX_CONNECTIONS
@@PACKET_ERRORS
@@CONNECTIONS
@@TIMETICKS
@@CPU_BUSY
@@TOTAL_ERRORS
@@DBTS
@@TOTAL_READ
@@IDLE
@@TOTAL_WRITE
@@IO_BUSY

So let's get started and test it out, I will create 2 UDF's one that uses GETDATE() and one that uses DATEADD() and GETDATE()

CREATE FUNCTION fnNonDeterministic ()
RETURNS DATETIME
AS
BEGIN
RETURN
GETDATE()
END
GO

CREATE FUNCTION fnNonDeterministicAdd (@Days int)
RETURNS DATETIME
AS
BEGIN
RETURN
DATEADD(d,@Days,GETDATE())
END
GO

--Let's see what the ouput is
SELECT dbo.fnNonDeterministic() AS DateReturned,
dbo.fnNonDeterministicAdd(2) AS DateAddReturned

1 comment:

Wally Banners said...

Whenever i am feeling like a know it all i head to your blog. No other blog on the net to i have a complete no understanding on what your saying.Its like your blog talks to me and says hey wally your dum huh. and i reply yeah man.