If you follow the various newsgroups on Microsoft SQL Server and other user

groups, you often see people asking, ‘Is there any way to use GETDATE() inside a

user defined function?’. The answer to this simple question is NO. But there is

way to do this. In this article I will explain how to you built_in functions

inside a UDF.

As we know that SQL Server does not allow you to use a Built-in functions that

can return different data on each call inside user-defined functions. The

built-in functions that are not allowed in user-defined functions are:

GETDATE

GETUTCDATE

NEWID

RAND

TEXTPTR

@@CONNECTIONS

@@CPU_BUSY

@@IDLE

@@IO_BUSY

@@MAX_CONNECTIONS

@@PACK_RECEIVED

@@PACK_SENT

@@PACKET_ERRORS

@@TIMETICKS

@@TOTAL_ERRORS

@@TOTAL_READ

@@TOTAL_WRITE

If you really want to use them inside a UDF, here is the way - create a view

called v_Built_in_funs and call the view inside your UDF. Here is the example:

CREATE VIEW v_Built_in_funs AS select getdate() systemdate, @@spid spid

The below UDF returns new objects created on current day:

CREATE FUNCTION fnGetNewObjects()
RETURNS TABLE
AS
RETURN ( 
SELECT name,CASE xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'L' THEN 'Log'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'Inlined table-function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure '
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE NULL
END OBJECT_TYPE
FROM sysobjects, v_Built_in_Funs 
WHERE CONVERT(VARCHAR(10),crdate,101) = CONVERT(VARCHAR(10),systemdate,101))

Call the UDF to get new objects created for the day:

SELECT * FROM fnGetNewObjects()