How do I use GetDate() in a Function?

I’ve been writing a User Defined Table (UDT) function based upon user defined conditions. One of the valid conditions utilises GetDate() to discover today’s date. However, if you simply use GETDATE() in a function you’ll get;
 Invalid use of ‘getdate’ within a function.
 
This is because you cannot use non-deterministic functions within a UDT. The "correct" solution is to pass the GETDATE() value as an argument to the function, however, this requires that I change all the calling code and I don’t want to do that, besides why should I!
 
So my workaround is to create a fully fledged SQL table whose role in life is simply to return GETDATE()…
 
CREATE TABLE [dbo].[DummyDate](
            [Today]  AS (getdate()),
            [Dummy] [nchar](1)
)
Then within the function you can access the value by…
declare @TodaysDate datetime
select @TodaysDate = Today from DummyDate
Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s