SQL Server: Using Current Date (GetDATE()) within User defined function

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

3 Comments on “SQL Server: Using Current Date (GetDATE()) within User defined function”

  1. 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

  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *