Thursday, September 27, 2012

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

Question:
Why I can't do:

DECLARE @dTimeStamp datetime
SELECT @dTimeStamp = getdate()

Inside of a function? ... I get an error:
Msg 443, Level 16, State 1, Procedure fcnName, Line 9

Invalid use of 'getdate' within a function.

Answer:
For some reasons Getdate() is not allowed in functions, and a few other places. This was a limitation in 2000, not anymore in 2005. Getdate() is nondeterministic function which are not allowed in 2000

Solution:
The work-around for that I use, if necessary, is to create a view based off of getdate() and call that view inside the function.

CREATE VIEW uvw_Now
AS
  SELECT getdate() AS now
GO

CREATE FUNCTION udf_Now()
RETURNS datetime
AS
BEGIN
  DECLARE @Now DATETIME

  SELECT @Now = now FROM uvw_Now

  RETURN @Now
END
GO

SELECT udf_Now()

No comments:

Post a Comment