Technical Article

How to obtain today's date within a UDF

,

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.

ALTER   FUNCTION [dbo].[getStartDate] (@aDate datetime)  
RETURNS datetime AS  
BEGIN 
-- assumes the getdate table and view exist with 1 row
declare @thisStartDate datetime
if @aDate=CONVERT(DATETIME, '1753-01-01 00:00:00', 102)
select @thisStartDate=today from dbo.vw_getDate
else
set @thisStartDate=@aDate
return @thisStartDate
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating