Many times, i have been troubled in scenario where the Current system Date cannot be referred using GETDATEI) within the User Defined Functions. This is obviously the non-deterministic nature of GETDATE() function and User defined functions do not allo their usage. Here is a tip i followed earlier which worked pretty well to overcome this constraint.
1. Create a view as following
CREATE VIEW CurrentDate
SELECT GETDATE() AS CurrentDateTime, CONVERT(DATE, GETDATE()) AS Today, DATEPART(DAY, GETDATE()) AS [Day],
DATEPART(MONTH, GETDATE()) AS [Month], DATEPART(YEAR, GETDATE()) AS [Year],
DATEPART(WEEKDAY, GETDATE()) AS [WeekDayNumber], DATENAME(WEEKDAY, GETDATE()) AS [WeekDayName],
DATEPART(WEEK, GETDATE()) AS [WEEK],
DATEPART(QUARTER, GETDATE()) AS [QuarterNumber],
CONVERT(CHAR(4),DATEPART(YEAR, GETDATE()))+'-Q'+CONVERT(CHAR(1),DATEPART(QUARTER, GETDATE())) AS [QuarterName],
DATEPART(DAYOFYEAR, GETDATE()) AS [DayOfYear],
DATEPART(ISOWK, GETDATE()) AS [ISOWK]
2. you can now use this view, which always returns one record within the User defined functions by referring to the columns of the CurrentDate view.
This seems to be a “hack” and it is supported up to SQL 2K8.
Any other better ideas will be appreciated .