How to use Getdate() in user defined Function

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

Transact-SQL (2000)

How to use Getdate() in user defined Function

Author

Topic

mdanwerali
Starting Member

30 Posts

Posted - 2002-10-30 : 02:34:16

hi,

I am tring to use getdate() in a user defined function but by doing this i am getting the following error:

Server: Msg 443, Level 16, State 1, Procedure NewEqueries, Line 5
Invalid use of 'getdate' within a function.

the original function is :

Create FUNCTION NewEqueries(@cat_id VARCHAR,@deptno int) RETURNS int AS
BEGIN
Declare @query_cnt int
SELECT @query_cnt = COUNT(DQDDFMID) FROM DFQUESDETAILS
WHERE DQDDFMID=@cat_id AND DQDSTATUS='Y' AND DQDDATE BETWEEN GetDate() -4 AND GetDate() AND
DQDQUESID IN (SELECT DISTINCT DFDQUESID FROM DFDEPARTMENTS WHERE DFDDEPTNO=@deptno )
RETURN @query_cnt
END

Please help me out...

Thanks in Advance

Md Anwer Ali

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-10-30 : 03:32:14

If you'd read BOL you'd notice that you CAN'T.

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-30 : 03:36:18

GetDate() is a non-deterministic function (i.e. its return value is different each time it is called), such a function cannot be called inside a user defined function. Instead pass the date returned by getdate() as a parameter to the function.

quote:

I am tring to use getdate() in a user defined function but by doing this i am getting the following error:

-ashok
http://www.unganisha.org

mdanwerali
Starting Member

30 Posts

Posted - 2002-10-30 : 03:59:23

is there any solution for this.. as i am not able to use RAND() function in user defined functions... is there any alternate to it.

Anwer

quote:

GetDate() is a non-deterministic function (i.e. its return value is different each time it is called), such a function cannot be called inside a user defined function. Instead pass the date returned by getdate() as a parameter to the function.

quote:

I am tring to use getdate() in a user defined function but by doing this i am getting the following error:

-ashok
http://www.unganisha.org

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-30 : 04:10:42

As i had said , change your function to take the date as a parameter :


Create FUNCTION NewEqueries(@cat_id VARCHAR,@deptno int, @curdate datetime) RETURNS int AS
BEGIN

so in your code you should be able to call it as :


declare @curdate datetime
select @curdate = getdate()
select NewEqueries('catid001', 1 , @curdate)

-ashok
http://www.unganisha.org

mdanwerali
Starting Member

30 Posts

Posted - 2002-10-30 : 04:17:40

thank you ashok.. i have another problem for random numbers..
i am using RAND() function to get the random number below the given number.

for example.

if i pass 10 then the random number could be from 1...10 how to get this value.

quote:

As i had said , change your function to take the date as a parameter :

Create FUNCTION NewEqueries(@cat_id VARCHAR,@deptno int, @curdate datetime) RETURNS int AS
BEGIN

so in your code you should be able to call it as :

declare @curdate datetime
select @curdate = getdate()
select NewEqueries('catid001', 1 , @curdate)

-ashok
http://www.unganisha.org

mr_mist
Grunnio

1870 Posts

Posted - 2002-10-30 : 04:19:47

quote:

if i pass 10 then the random number could be from 1...10 how to get this value.

Pass the random number as a paramater instead...

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-30 : 04:27:43

there is a very easy rule to generate a random number within a range.
if you want generate a random number between x & y,

(y-x) * rand() + x

will give a random number in the x<->y range.
so to generate random numbers between 1 and 10 ,


select (10-1)*Rand()+1

this will return a decimal between 1 and 10 like 4.33838,
so you can cast it as an integer :


select Cast(9*Rand()+1 as integer)

to return a natural number

quote:

if i pass 10 then the random number could be from 1...10 how to get this value.

-ashok
http://www.unganisha.org

Edited by - ashok on 10/30/2002 04:29:14

mdanwerali
Starting Member

30 Posts

Posted - 2002-10-30 : 04:49:50

i tried this, but the same thing is not working in my user defined function..... i wil be more thank full if u can tell me how to use the same randam function in my user defined function...

Anwer.

quote:

there is a very easy rule to generate a random number within a range.
if you want generate a random number between x & y,

(y-x) * rand() + x

will give a random number in the x<->y range.
so to generate random numbers between 1 and 10 ,

select (10-1)*Rand()+1

this will return a decimal between 1 and 10 like 4.33838,
so you can cast it as an integer :

select Cast(9*Rand()+1 as integer)

to return a natural number

quote:

if i pass 10 then the random number could be from 1...10 how to get this value.

-ashok
http://www.unganisha.org

Edited by - ashok on 10/30/2002 04:29:14

mr_mist
Grunnio

1870 Posts

Posted - 2002-10-30 : 05:19:21

quote:

i tried this, but the same thing is not working in my user defined function..... i wil be more thank full if u can tell me how to use the same randam function in my user defined function...

You can't. You can't use the Rand function within a user-defined function. You can't use any of the functions in the grey box in the explanation for "CREATE FUNCTION" in BOL within user-defined functions. You will need to pass the random figure into your function as a parameter.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-10-30 : 05:23:58

AGAIN, you are NOT reading and taking on board the help previously offered (mr_mist, rihardh, ashok)...sorry to be blunt, but reading and following the advice is VERY important.....You CANNOT call the RAND() function INSIDE a UDF. WHAT you can do is call RAND() outside the UDF and then pass this value into the UDF and then use it inside the UDF in whatever fashion you require.

example below....

dim a as float
a = RAND()
CALL myudf (a)

CREATE FUNCTION myudf(@inrand float) RETURNS int AS
BEGIN
myudf = 9 * @inrand + 1
END

(the syntax may not be correct .... but you should be able to sort that out yourself)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources