Invalid use of 'getdate' within a function.

Posted on 2006-05-12
Last Modified: 2010-03-19
Of course I ve seen the other questions about this subject but it's not entirely clear.
In the Northwind db, run this query:
select * from orders where Datediff("d",[OrderDate],Getdate()) > 3100

Now I want to use it in a function, so I would create the function like this:

create FUNCTION dbo.udf_orders (@mydate datetime)
returns table
select * from orders where Datediff("d",[OrderDate],@mydate) > 3100

All fine. (Would make sense to pass the 3100 as a parameter as well but this is not relvant for the question.)

But calling the function causes a problem:
Select * from dbo.udf_orders(GetDate())  gives an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

Any ideas how to use this function?

Question by:yellow1234
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    declare @d date
    set @d = getdate()
    Select * from dbo.udf_orders(@d)  
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    You can't call getdate() inside a function,
    Better you pass it as an argument to the function
    LVL 75

    Accepted Solution

    I thinkmissed the datatype, it should be

    declare @d datetime            --- changed date to datetime
    set @d = getdate()
    Select * from dbo.udf_orders(@d)


    Author Comment

    mmmm I ve got a lot of views which are all querying the same tables but use different filters... I wanted to create only one function and pass different parameters, instead of creating loads of views... but it seems that this issue complicates matters because I would like the views in other views or stored procedures and treat them as they were tables... Any comments?
    Thanks for your fast replies.
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    You can go for Procedures,as you can pass the arguments..

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now