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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

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 *