Using getdate() in a UDF

You might have tried using the getdate() non-deterministic function within a UDF some time and would have gotten an error stating:

“Invalid use of ‘getdate’ within a function”

That is because getdate() is a non-deterministic function (functions that do not always return the same result from the same input) and because of that restriction in SQL Server, it cannot be used within a function.  There are ways to get around this limitation though.  One of the ways is to create a view and then use it, for example:

create view GETDATE_VIEW
as
select getdate() as CurrentDateTime
go

create function dbo.GETDATE_FUNCTION
(
@datetime datetime
)
returns bit
as
begin
declare @rc bit
select @rc = case when @datetime < CurrentDateTime then 1 else 0 end
from GETDATE_VIEW

return @rc
end
go

select dbo.GETDATE_FUNCTION(getdate())

—–
0

However, do note that this view hack can return un-expected results depending upon how you are using it.  Here are two UDFs, one using the view and the other one does not:

CREATE FUNCTION UDF_1()
RETURNS datetime AS
BEGIN
RETURN (SELECT currentdatetime FROM getdate_view)
END
GO
CREATE function UDF_2
(@dt datetime)
RETURNS datetime
AS
BEGIN
RETURN @dt
END
GO
–Now, let’s see what we get when we execute a query with both the UDFs

declare @dt datetime
set @dt = current_timestamp
select distinct dbo.UDF_1(), xtype from sysobjects
select distinct dbo.UDF_2(@dt), xtype from sysobjects

On my instance and the database that I was using, the first one returned 16 rows and the second one returned 10 rows.  This has to do with when the function gets evaluated and thus the view one can return different results for records and end up with more rows within the same statement execution since it is getting evaluated per record.

Another way of doing is by using an openquery():

create FUNCTION dbo.UDF_3
()
RETURNS datetime
AS
BEGIN
DECLARE @dt datetime
SELECT @dt = getdate_data FROM OPENROWSET(‘SQLOLEDB’, ‘Server=.;Trusted_Connection=yes;’,'SELECT getdate() AS getdate_data’)
RETURN @dt
END
GO

select distinct dbo.UDF_3(), xtype from sysobjects

This returned a lot more distinct values for the getdate() values.  So, your best bet would be to pass in a parameter after assigning it the getdate() value and then use it within the UDF or if possible, use a stored procedure instead of the UDF.