getdate in UDF

hi,
I am not able to use today date by using getdate() in UDF. Is there
anyway to work around?
Thanks
Edhttp://www.aspfaq.com/2439
http://www.aspfaq.com/
(Reverse address to reply.)
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:917F94C1-3231-4854-B15C-975F2734FD61@.microsoft.com...
> hi,
> I am not able to use today date by using getdate() in UDF. Is there
> anyway to work around?
> Thanks
> Ed|||No, you have to get the current date before you call the UDF, and pass that
value as another UDF Parameter. This is because UDFs must be "determinisiti
c"
"Ed" wrote:

> hi,
> I am not able to use today date by using getdate() in UDF. Is there
> anyway to work around?
> Thanks
> Ed|||if order to use getdate() in UDF, i could not do something like
select * from dbo.returndate(getdate())
it generates an error...
or
I have to use stored procedure instead'
Ed
"Aaron [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2439
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:917F94C1-3231-4854-B15C-975F2734FD61@.microsoft.com...
>
>|||Well, what is the error message? Is your function a table-valued function,
or a user-defined function? If it is a user-defined function, use SELECT
dbo.returndate(getdate()) not SELECT * FROM ... I don't see any SELECT *
FROM examples in the link I posted.
http://www.aspfaq.com/
(Reverse address to reply.)
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:FBAEFA21-EA22-400F-8BBE-7313224DBD90@.microsoft.com...
> if order to use getdate() in UDF, i could not do something like
> select * from dbo.returndate(getdate())
> it generates an error...
> or
> I have to use stored procedure instead'
> Ed
>
> "Aaron [SQL Server MVP]" wrote:
>|||No, they do not have to be deterministic, but you cannot use
non-deterministic system functions in user-defined functions, which
getdate() is. Any function that selects from a table or view will be
non-deterministic. You can create a view that contains getdate() and select
from it, if you want. Best to pass it as a parm though:
create function returnTime
(
@.date datetime
)
returns datetime
as
begin
return @.date
end
go
select dbo.returnTime(getdate())
go
returns:
---
2005-03-05 19:20:03.650
Well, if you happen to run it at the same time I did, but it will return a
data value :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:666097DD-5A60-4F35-9CBE-503C54C38EB3@.microsoft.com...
> No, you have to get the current date before you call the UDF, and pass
> that
> value as another UDF Parameter. This is because UDFs must be
> "determinisitic"
> "Ed" wrote:
>|||Yes you're right. Got that ...
"Louis Davidson" wrote:

> No, they do not have to be deterministic, but you cannot use
> non-deterministic system functions in user-defined functions, which
> getdate() is. Any function that selects from a table or view will be
> non-deterministic. You can create a view that contains getdate() and sele
ct
> from it, if you want. Best to pass it as a parm though:
> create function returnTime
> (
> @.date datetime
> )
> returns datetime
> as
> begin
> return @.date
> end
> go
> select dbo.returnTime(getdate())
> go
> returns:
>
> ---
> 2005-03-05 19:20:03.650
> Well, if you happen to run it at the same time I did, but it will return a
> data value :)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:666097DD-5A60-4F35-9CBE-503C54C38EB3@.microsoft.com...
>
>|||You can use a view with getdate() inside a udf but you are fololing the
optimiser and this can produce invalid (i.e. incorrect) results so I would
not try it and certainly not use it in a production system.
"Louis Davidson" wrote:

> No, they do not have to be deterministic, but you cannot use
> non-deterministic system functions in user-defined functions, which
> getdate() is. Any function that selects from a table or view will be
> non-deterministic. You can create a view that contains getdate() and sele
ct
> from it, if you want. Best to pass it as a parm though:
> create function returnTime
> (
> @.date datetime
> )
> returns datetime
> as
> begin
> return @.date
> end
> go
> select dbo.returnTime(getdate())
> go
> returns:
>
> ---
> 2005-03-05 19:20:03.650
> Well, if you happen to run it at the same time I did, but it will return a
> data value :)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:666097DD-5A60-4F35-9CBE-503C54C38EB3@.microsoft.com...
>
>|||What do you mean, invalid? I have not seen this mentioned before. I have
never used one, but have seen it mentioned before that you might get invalid
(ie wrong) results.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:97E762F6-33AF-45E7-AFE3-B8CA0E44BE90@.microsoft.com...
> You can use a view with getdate() inside a udf but you are fololing the
> optimiser and this can produce invalid (i.e. incorrect) results so I would
> not try it and certainly not use it in a production system.
> "Louis Davidson" wrote:
>|||See the link to Tibor's explanation here:
http://www.aspfaq.com/2439
http://www.aspfaq.com/
(Reverse address to reply.)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ue6naRoIFHA.2356@.TK2MSFTNGP12.phx.gbl...
> What do you mean, invalid? I have not seen this mentioned before. I have
> never used one, but have seen it mentioned before that you might get
invalid
> (ie wrong) results.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:97E762F6-33AF-45E7-AFE3-B8CA0E44BE90@.microsoft.com...
would
return
>
--
pass
there
>sql