posted on Sunday, February 12, 2006 9:47 AM
by
amachanic
GETDATE in a UDF?
An extremely common question in forums is, "How can I use the GETDATE()
function in a UDF?" Because the GETDATE() function is
nondeterministic, it is not allowed in SQL Server 2000 UDFs.
To date, I've always made it a point to answer the question the same
way: "Add a DATETIME parameter to the UDF and pass it in." Yes, you
can create a view that selects GETDATE() and select from the view in
your UDF to work around the restriction, but that really seems like a
dirty hack to me. I'm not sure why, but passing in the date just feels
cleaner...
But after answering this question the same way so many times, I was shocked to read a
blog post by Louis Davidson
in which he points out that the restriction has been lifted in SQL
Server 2005. You can now use GETDATE() within a UDF; no need to either
pass it in or create the view.
Great job spotting that change, Louis! I never would have thought to
look for a modification of that behavior. This is one of those little
tiny annoyances that crops up every once in a while (always at a very
bad time), and it's good to know that we no longer have to be concerned
with how to work around this issue.