October 23, 2003 at 3:46 pm
I created a function that takes a date as a parameter. I function executes fine when I call it with the following syntax
select * from fn_ibc_PerCapitaStmtContact('10/22/2003')
But what I would like to be able to do is to execute it with the following syntax
select * from fn_ibc_PerCapitaStmtContact(getdate())
This however gives me an error. Does anyone know if it's possible to get this to work?
October 23, 2003 at 6:00 pm
Hello. Getdate() will work. As a test I created this function:
CREATE FUNCTION x1(@x datetime)
RETURNS datetime AS
BEGIN
return @x
END
and ran this in QA:
SELECT dbo.x1(GETDATE()) AS x
Everett
Everett Wilson
ewilson10@yahoo.com
October 24, 2003 at 7:17 am
Hi ewilson, thanks for your response. I tried your example and it worked for me too. However, I still can't get it to work for my function. I think the difference may be that your example function is a scalar function whereas my function is a Multi-statement table-valued function. It appears to make a difference. Here is an example of my function that illustrates the error that I am getting:
CREATE TABLE [FunctionTest] (
[FIRST_NAME] [varchar] (35) NOT NULL CONSTRAINT [DF_FunctionTest_FIRST_NAME] DEFAULT (''),
[LAST_NAME] [varchar] (35) NOT NULL CONSTRAINT [DF_FunctionTest_LAST_NAME] DEFAULT ('')
) ON [PRIMARY]
GO
INSERT INTO FunctionTest VALUES ('firstname1', 'lastname1')
INSERT INTO FunctionTest VALUES ('firstname2', 'lastname2')
GO
CREATE FUNCTION fn_ibc_PerCapitaStmtContact(@AsOfDate AS DATETIME)
RETURNS @ContactInfo TABLE
(
FIRST_NAME VARCHAR(35),
LAST_NAME VARCHAR(35)
)
AS
BEGIN
INSERT INTO @ContactInfo
SELECT FIRST_NAME, LAST_NAME FROM FunctionTest WHERE FIRST_NAME = 'firstname1'
INSERT INTO @ContactInfo
SELECT FIRST_NAME, LAST_NAME FROM FunctionTest WHERE FIRST_NAME = 'firstname2'
RETURN
END
GO
--this gives me an error
SELECT * FROM fn_ibc_PerCapitaStmtContact(GETDATE())
--this does not
SELECT * FROM fn_ibc_PerCapitaStmtContact('10/24/2003')
GO
DROP TABLE FunctionTest
DROP FUNCTION fn_ibc_PerCapitaStmtContact
GO
Thanks...
October 24, 2003 at 7:39 am
October 24, 2003 at 8:17 am
Thanks for the link Jonathan.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply