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

Leave a Reply

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

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

Facebook photo

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

Connecting to %s