system functions as function parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

system functions as function parameters

Author

Topic

imStuck
Starting Member

8 Posts

Posted - 2006-02-08 : 20:47:44

Hi,
I have a database function that takes a datetime for a parameter.
I want to call my function using todays date as a parameter like so.
SET @var = dbo.myfunction(getDate())

It won't work.

Ive worked around it by using a datetime variable that has been set to getDate() as the parameter.
Any suggestions?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 21:07:29

You are mistaken. They work fine with scaler functions, but do not work with table valued functions.

This function works fine with getdate()
SELECT [dbo].[F_ISO_WEEK_OF_YEAR](getdate())

You can find the F_ISO_WEEK_OF_YEAR function here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

You cannot use a system function, or even an expresion for a parameter of a table valued function.

Works:
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,2000)

Does not work:
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,2000-1)

You can find the F_TABLE_NUMBER_RANGE function here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 02:21:45

Also you can try

Declare @date DateTime
set @date=getdate()
SET @var = dbo.myfunction(@date)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 05:49:17

quote:
Originally posted by imStuck

Hi,
I have a database function that takes a datetime for a parameter.
I want to call my function using todays date as a parameter like so.
SET @var = dbo.myfunction(getDate())

It won't work.

Ive worked around it by using a datetime variable that has been set to getDate() as the parameter.
Any suggestions?

What won't work ? the SET ? or passing getdate() as parameter ?
Post your function here

This is fine :

SET @var = dbo.myfunction(getDate())

This is also fine :

SELECT @var = dbo.myfunction(getDate())

The following scripts works

create function dbo.date_only(@dt datetime)
returns datetime
as
begin
	return dateadd(day, 0, datediff(day, 0, @dt))
end

declare	@today	datetime
set 	@today = dbo.date_only(getdate())
select 	@today

select 	@today = dbo.date_only(getdate())
select 	@today

----------------------------------
'KH'
everything that has a beginning has an end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 06:40:32

quote:
declare @today datetime
set @today = dbo.date_only(getdate())
select @today

select @today = dbo.date_only(getdate())
select @today

I think you meant first part as

declare @today datetime
set @today=getdate()
set @today = dbo.date_only(@today)
select @today

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 07:44:29

Yes. you are right. Was meant to demostrate passing in a variable to a function and as well as using a system function as a parameter

----------------------------------
'KH'
everything that has a beginning has an end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources