Archive

Archive for the ‘Sql Server 2000’ Category

[T-SQL] How do I use GETDATE() within a User-Defined Function (UDF)?

Thursday, 22 May, 2008 2 comments

SQL Server 2000 added the support for user-defined functions, but there are a few limitations which can be roadblocks at first. One is that you cannot use a non-deterministic function within a UDF, e.g. GETDATE(). So, let’s say you are trying to create a function that returns this moment, but a day in the future (e.g. tomorrow at this exact time). You would think about it this way:

CREATE FUNCTION dbo.addDay()
RETURNS DATETIME
AS
BEGIN
DECLARE @dt DATETIME
SET @dt = DATEADD(DAY, 1, GETDATE())
RETURN @dt
END

But you will get this error message:
Server: Msg 443, Level 16, State 1, Procedure addDay, Line 6
Invalid use of 'getdate' within a function.

Read more…