[T-SQL] 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.

article source

update: here real sample

USE aspg
go
if exists(select name from sysobjects where name='_ufn_Since' and type = 'FN')
drop function dbo._ufn_Since
go
create function dbo._ufn_Since(@today datetime, @fromdate datetime) returns varchar(100)
as
begin
declare @since Nvarchar(100)
select @since = 'С момента регистрации: '+ case when datediff(mi,@fromdate,@today) < 60 then cast(datediff(mi,@fromdate,@today) as varchar ) + ' [мин]'
when datediff(mi,@fromdate,@today) < 1440 then cast(datediff(mi,@fromdate,@today)/60 as varchar ) + ' [час] ' +
cast(datediff(mi,@fromdate,@today)%60 as varchar ) + ' [мин]'
when datediff(mi,@fromdate,@today) > 1440 then cast(datediff(dd,@fromdate,@today) as varchar ) + ' [день] ' +
cast(datediff(hh,@fromdate,@today)%24 as varchar) + ' [час] ' +
cast(datediff(mi,@fromdate,@today)%60 as varchar ) + ' [мин]'
else ''
END
if (@since='') set @since = 'error'
return  @since
end
go
select dbo._ufn_Since(getdate(),data) from reg
go

result set looks like as:

С момента регистрации: 48 [день] 5 [час] 39 [мин]
С момента регистрации: 48 [день] 5 [час] 39 [мин]
С момента регистрации: 18 [день] 4 [час] 22 [мин]
С момента регистрации: 18 [день] 4 [час] 5 [мин]
С момента регистрации: 18 [день] 4 [час] 5 [мин]
С момента регистрации: 18 [день] 4 [час] 4 [мин]
С момента регистрации: 7 [день] 3 [час] 57 [мин]
С момента регистрации: 4 [час] 31 [мин]
С момента регистрации: 3 [час] 59 [мин]
С момента регистрации: 3 [час] 13 [мин]