How to obtain today's date within a UDF

Total article views: 133 | Views in the last 30 days: 18

 Rate this |   Join the discussion |  Briefcase

As you know, if you've written even a modested amount of UDFs, they like to be deterministic in fact they insist on it, and the most undeterministic value you are likely to want is today's date.
Yes, you can use calls to extended stored procedures but I like the simplicity and versatility of this solution.
The answer is.... UDFs can select from views and views can return calculated fields. So, create a table with one field and one dummy row - the data does not matter. Create a view on this table (say, vw_getDate) that returns GETDATE() as the only field. Then it's just a case of...

declare @thisStartDate datetime
select @thisStartDate=today from dbo.vw_getDate

Obviously, thsi can be extended for other non-deterministic problems.

I thank you. Goodnight.
ps. the sample below was required because Navision stores blank sales and purchase price start and end dates as '01-01-1753' which as we all know has somethign to do with the Gregorian Calendar. Personally, I would have prefered nulls.

Total article views: 133 | Views in the last 30 days: 18