CODEWITHSUNDEEP
sql-server
fox
fox

Reputation: 1

SQLServer using GETDATE() as a parameter of table function in SQL query

I have a query:

select * from dbo.GetTableTest(GETDATE(), GETDATE());

select * from dbo.GetTableTest('2020-02-02', '2020-02-02');

first one gives syntax error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.. Second query gives no error. My complete example function header:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--##
alter FUNCTION [dbo].[GetTableTest]
(
@dataod datetime,
@datado datetime
)
RETURNS TABLE
as return
select
fo.DNRWGKONTR NrFaktury,cast(fo.DWPLYNELO as DATETIME) DataWplywuFaktury
FROM
PEVREJVATZAKUP1 fo
where
cast(fo.DDATA as DATETIME) between @dataod and @datado

I've try many variations but the result is the same. How can I write this kind of query using some date functions as a parameters?

Upvotes: 0

Views: 324

Answers (1)

droebi
droebi

Reputation: 946

Edit:

You cannot use ...MyFunction( SELECT GETDATE())

Use instead: ...MyFunction(GETDATE())

Old answer:

Remove the comma on your function declaration:

ALTER FUNCTION [dbo].[MyFunction]
(   
    @dataod datetime, -- <- only one param, only use a comma here if you have more than one params
) -- <- this is the bracket which causes your error
RETURNS TABLE 
AS
RETURN 
(
    SELECT @dataod AS DateValue
)
GO

Fix:

ALTER FUNCTION [dbo].[MyFunction]
(   
    @dataod datetime
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT @dataod AS DateValue
)
GO

Call:

SELECT * FROM MyFunction(GETDATE())

Output:

DateValue
2020-10-01 10:13:59.957

Upvotes: 1

Related Questions