Use GETDATE() within a UDF

  • recently i had a need to use GETDATE() within a function.  The result?

    Server: Msg 443, Level 16, State 1, Procedure ProofOfConcept, Line 10

    Invalid use of 'getdate' within a function.

    I looked around the internet and could not find a workaround, so i made my own:

    -- create a view that can be accessed from a function

    CREATE  VIEW dbo.vw_GetDate

    AS

    SELECT GETDATE() AS Now

    GO

    -- create a reusable function for quickly and easily getting date-time

    CREATE  FUNCTION dbo.Now()

    RETURNS datetime

    AS

    BEGIN

     DECLARE @Tmp datetime ;

     SELECT @Tmp = Now FROM vw_GetDate ;

     RETURN @Tmp ;

    END

    GO

    -- now use it in a proof of concept function

    CREATE FUNCTION dbo.ProofOfConcept()

    RETURNS datetime

    AS

    BEGIN

     --RETURN GETDATE() ; -- this no workie workie

     RETURN dbo.Now() ;

    END

    GO

    -- now use it in a sentance

    SELECT dbo.ProofOfConcept() AS [GETDATE()]

     

  • There is no case when you need GETDATE() within a UDF.

    _____________
    Code for TallyGenerator

  • Sergiy is absolutely correct.

    If a UDF needs the current date, pass the value as a parameter.

    Here is a story regarding the use of UDFs that include table access.

    About 2 years ago, a prior client called on a Wednesday regarding major performance problems with a new system that ran for the first time on Monday.

    They had developed the db portion with extensive use of UDF that included table access. That week-end, I and another DBA ripped out all of the UDFs and replaced with the appropriate sql statements resulting in solving the performance problem.

    Do not put table access in stored procedures !

    SQL = Scarcely Qualifies as a Language

  • You mean in functions?

    _____________
    Code for TallyGenerator

  • Sergiy: Thanks for catching the typing error

    "Do not put table access in stored procedures !"

    should be

    "Do not put table access in FUNCTIONS!"

    SQL = Scarcely Qualifies as a Language

  • Well, let me disagree with the absolutness of that statement. It depends. You can very well put table access inside UDF's, sometimes it's even the best thing to do - BUT... everyone that is thinking 'UDF' as a solution need to be very clear and understand that UDF's are not a magic wand that will magically transform, say a cursor to a setbased solution - it's rather the opposite. If not careful, it's more likely that a perfectly good setbased piece of code will instead be turned into a 'hidden cursor' if UDF's are used without discretion.

    So, it depends. I'd say 'be careful' - I won't stretch it as far as to say 'never access tables in UDFs'.

    ..just my .02 though

    /Kenneth

  • You are absolutely right about "hidden cursor".

    But the best and most reliable way to create hidden cursor is to put table access inside UDF!

    Yes, nothing is absolute, and sometimes table access in UDF may be used, as well as cursors.

    But very, VERY, VERY CAREFUL, rarely, better never.

    Right function produces result from parameters supplied. And not from anything else. Otherwise result of the function is unpredictable.

    If you need to workout values in tables, put function into view.

    But not view into function.

    P.S. And there is still one "never" - you never need GETDATE() inside UDF.

    _____________
    Code for TallyGenerator

  • P.S. And there is still one "never" - you never need GETDATE() inside UDF.

    Just curious, can you expand on this?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Can you give an example?

    When you possibly need it?

    _____________
    Code for TallyGenerator

  • I asked first

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Theory:

    Getdate() is just datetime value. It may be supplied to UDF as a parameter. You set GETDATE() value to this parameter when you call UDF. It makes UDF deterministic and more universal.

    Practice:

    In physics there is a principle: it's not possible because it could never happen.

    In other words, if there are no cases when it's possible, it's considered impossible.

    Show me the case when you need it.

    _____________
    Code for TallyGenerator

  • When you reread my first reply closely, you'll noticed that I didn't say "I need it". Actually I don't need it, but you made this bold statement, so I thought I'd ask for your reasoning here. 

    Coming to think about it, there might be some valid reasons when you have a table-valued function. Yes, I am aware that a parameterless inline TVF is basically nothing but a view. However, there might be cases where you need conditional or more complex logic which you can't put in a view. UDF's allow you to define variables and allow procedural code. And depending on your requirements you might need GETDATE() for your code for some reasons. Sure, you can use a stored procedure, but what when you need to join the results to some other table?

    I wouldn't say "Never". As Kenneth would say: "It depends..."

    Btw, using GETDATE() inside a view and call this view from the UDF can yield incorrect results:

    Consider this:

    USE northwind

    GO

    CREATE VIEW foolview

    AS

    SELECT GETDATE() AS Jetzt

    GO

    CREATE FUNCTION fool_me()

    RETURNS DATETIME

    AS

    BEGIN

      RETURN

      (

      SELECT Jetzt

        FROM foolview

      )

    END

    GO

    CREATE function you_dont_fool_me(@Jetzt datetime)

    RETURNS DATETIME

    AS

    BEGIN

      RETURN @Jetzt

    END

    GO

    DECLARE @Jetzt datetime

    SET @Jetzt = GETDATE()

    --Test 1 viele Zeilen

    SELECT DISTINCT dbo.fool_me()

      FROM [Order Details] AS od

    INNER JOIN Orders AS o

    ON o.OrderId = od.OrderID

    --Test2 eine Zeile

    SELECT DISTINCT dbo.you_dont_fool_me(@Jetzt)

      FROM [Order Details] AS od

    INNER JOIN Orders AS o

    ON o.OrderId = od.OrderID

    GO

    DROP FUNCTION fool_me

    DROP FUNCTION you_dont_fool_me

    DROP VIEW foolview

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I may change my mind on UDFs.

    I now believer "deny create function to public" is the most appropriate.

    Public includes everyone except system adminstrators and does include users with ddl_admin or db_owner database roles.

    I am in the process of writting an article and a set of test cases but here is an interesting post:

    From a sql-server-performance posting (edited):

    When using a UDF in the where, run time goes from < .01 seconds to 2.14 minutes (134 seconds)

    -- Comment this is 13 thousand times slower

    The SQL is:

    SELECT ExpenseDetailID

    FROM ExpenseDetail

    WHERE UPPER(SUBSTRING(dbo.Dec(ReferenceNumber)

    ,CHARINDEX('-',dbo.Dec(ReferenceNumber))+ 1

    , LEN(dbo.Dec(ReferenceNumber)))) = 320

    AND Flag 'D'

    The object "dbo.Dec" is a user defined function.

    SQL = Scarcely Qualifies as a Language

  • I was calling a function from a stored proc that accepted 3 parameters and returned a value based on how those parameters related to each other.

    4th parameter would have been GETDATE(), but I felt it was an annoyance to have to pass the current date-time when calling the function, when the system could just as well produce that value all by itself inside the function.

    I don't think querying a view is going to have performance issues similar to a table scan, as was your example.

     

  • It's an annoyance to pass a parameter but not to have a view dedicated just to display GETDATE()?

    And when one day you'll need to compare dates in function not to current date but to the same moment yesterday whay you're gonna do? Write another function or reser system clock?

    And what if you use a server placed in another time zone?

    _____________
    Code for TallyGenerator

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply