• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Invalid use of 'getdate' within a function.

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?

  • 3
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
declare @d date
set @d = getdate()
Select * from dbo.udf_orders(@d)  
Aneesh RetnakaranDatabase AdministratorCommented:
You can't call getdate() inside a function,
Better you pass it as an argument to the function
Aneesh RetnakaranDatabase AdministratorCommented:
I thinkmissed the datatype, it should be

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

yellow1234Author Commented:
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.
Aneesh RetnakaranDatabase AdministratorCommented:
You can go for Procedures,as you can pass the arguments..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now