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 AS 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 🙂 .
Regards
Sriram Subramanyan
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
Alteast SQL Server 2005/2008/2008 R2 supports to use GETDATE() inside a UDF.
Try running following code:
view source
print
1 CREATE FUNCTION dbo.myfun () RETURNS DATETIME
2 BEGIN
3 DECLARE @currentdatetime DATETIME
4 SET @currentdatetime = GETDATE()
5 RETURN @currentdatetime
6 END
7 go
8 SELECT dbo.myfun () As CurrentDatetime
yes Rakesh, this is true, and thanks for correcting me. SQL Server 2K8 is allowing it, am not sure about SQL Server 2K5. But, i have faced this problem with SQL 2K earlier, and had this handle implemented in few of my projects.
I changed the DB compatibilty to 80.Seems to be working for me.