How do I use GETDATE() within a User-Defined Function (UDF)?

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.

 
So, instead, you should handle it so that you pass non-deterministic values into the function, for example: 
 

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

 
Then you can call it like this: 
 

DECLARE @dt DATETIME  SET @dt = GETDATE() -- or CURRENT_TIMESTAMP  SELECT dbo.addDay(GETDATE())

 
Another idea is to use OPENQUERY as follows: 
 

CREATE FUNCTION dbo.addDay()  RETURNS DATETIME  AS  BEGIN      DECLARE @dt DATETIME      SELECT @dt = dt + 1           FROM OPENQUERY          (              YourServer,              'SELECT dt = GETDATE()'          )      RETURN @dt  END  GO

 
If YourServer is not configured for Data Access, you will get this error: 
 

Server: Msg 7411, Level 16, State 1, Procedure addDay, Line 6  Server 'YourServer' is not configured for DATA ACCESS.

 
You will need to set the local linked server to allow data access in order to accept calls through OPENQUERY. You can do this using sp_serveroption: 
 

EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE

 
A less efficient (and less accurate) workaround is to use an intermediate view. This simplifies your coding a bit, since you no longer have to pass GETDATE() into the function. First, create the view: 
 

CREATE VIEW dbo.vGETDATE  AS  BEGIN      SELECT vGETDATE = GETDATE()  END

 
Then, create the function: 
 

CREATE FUNCTION dbo.addDay()  RETURNS DATETIME  AS  BEGIN      DECLARE @dt DATETIME      SELECT @dt = [vgetdate]+1 FROM vGETDATE      RETURN @dt  END

 
And you can call it like this: 
 

PRINT dbo.addDay()

 
However, this has been proven to produce inconsistent results, such as this issue that Tibor Karaszi has pointed out: 
 

USE Northwind  GO     CREATE VIEW dbo.vCurrentDateTime  AS       SELECT gd = GETDATE()  GO       CREATE FUNCTION dbo.getFromView()  RETURNS DATETIME  AS   BEGIN       RETURN (SELECT gd FROM vCurrentDateTime)   END   GO    CREATE FUNCTION dbo.getFromSelf()  (      @dt DATETIME  )  RETURNS DATETIME  AS   BEGIN       RETURN @dt   END   GO       DECLARE @dt datetime   SET @dt = GETDATE()    SELECT DISTINCT dbo.getFromView()       FROM Orders o      INNER JOIN [Order Details] od      ON o.OrderId = od.OrderID     SELECT DISTINCT dbo.getFromSelf(@dt)       FROM Orders o      INNER JOIN [Order Details] od      ON o.OrderId = od.OrderID   GO     DROP FUNCTION dbo.getFromView(), dbo.getFromSelf()  DROP VIEW vCurrentDateTime

 
On my machine, the first query produced anywhere from 8 to 10 rows (and this changed with every refresh), while the second query *always* produced exactly one. This is because using a non-deterministic function within the view leads to GETDATE() being evaluated multiple times as the query runs. Now, this is a contrived example, and is unlikely to resemble anything you're doing in the real world; however, I wanted to include this discrepancy for completeness, and in support of using other methods to getting GETDATE() passed into your function. My preference is to use a local variable and pass it into the function from the calling script... 
 

---

The difference between a deterministic and non-deterministic function is that the former always produces the same output (given the same input), whereas the latter can produce a different output each time they are called (again, with the same input). This is why system functions like GETDATE() are non-deterministic; they will produce different results each time you call them. 
 
In Books Online, there is a topic called 'Deterministic and Nondeterministic Funct...' which goes into much more detail about this issue, and lists the inbuilt functions organized by whether they are always, sometimes, or never deterministic.