[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • 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..

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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