|
|
You're in the
sql server programming
group:getdate in UDF
sql server programming:
I am not able to use today date by using getdate() in UDF. Is there anyway to work around? Thanks
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" [quoted text, click to view] "Ed" wrote:
> hi, > I am not able to use today date by using getdate() in UDF. Is there > anyway to work around? > Thanks
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 [quoted text, click to view] "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... > > hi, > > I am not able to use today date by using getdate() in UDF. Is there > > anyway to work around? > > Thanks > > Ed > >
http://www.aspfaq.com/2439
-- http://www.aspfaq.com/ (Reverse address to reply.) [quoted text, click to view] "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
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.) [quoted text, click to view] "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: > > > 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... > > > hi, > > > I am not able to use today date by using getdate() in UDF. Is there > > > anyway to work around? > > > Thanks > > > Ed > > > > > >
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 :) [quoted text, click to view] "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: > >> hi, >> I am not able to use today date by using getdate() in UDF. Is there >> anyway to work around? >> Thanks >> Ed
Yes you're right. Got that confused...
[quoted text, click to view] "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 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: > > > >> hi, > >> I am not able to use today date by using getdate() in UDF. Is there > >> anyway to work around? > >> Thanks > >> Ed > >
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. [quoted text, click to view] "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 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: > > > >> hi, > >> I am not able to use today date by using getdate() in UDF. Is there > >> anyway to work around? > >> Thanks > >> Ed > >
A simple example is
create view d as select d = getdate() go drop function x create function x(@i int) returns datetime as begin return (select d from d) end go select dbo.x(s1.id), count(*) from master..sysobjects s1, master..sysobjects s2 group by dbo.x(s1.id) order by dbo.x(s1.id) Nigel Rivett www.mindsdoor.net *** Sent via Developersdex http://www.developersdex.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 :) [quoted text, click to view] "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: > >> 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: >> > >> >> hi, >> >> I am not able to use today date by using getdate() in UDF. Is there >> >> anyway to work around? >> >> Thanks >> >> Ed >> >> >>
See the link to Tibor's explanation here:
http://www.aspfaq.com/2439 -- http://www.aspfaq.com/ (Reverse address to reply.) [quoted text, click to view] "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... > > 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 > >> 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: > >> > > >> >> hi, > >> >> I am not able to use today date by using getdate() in UDF. Is there > >> >> anyway to work around? > >> >> Thanks > >> >> Ed > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |