GetDate() in UDF

I understand that you can not use GetDate() in a UDF.
I also can not pass a parameter to the UDF (because this is an Access to SQL
Server conversion, and the program that calls this UDF does not pass any
parameter to it).
So, I am trying to create a View for GetDate() like the codes below.
The issue is, I also need to select from another table (tblA) besides
getting the GetDate() value.
Is the following codes correct and efficient on how to do that ?
Thanks.
create view get_date
as
select getdate()dt
CREATE function dbo.udftemp()
returns @.myTable TABLE(id varchar(10),datex datetime)
AS BEGIN
INSERT INTO @.myTable(id,datex)
select tblA.id,dt
FROM tblA, get_date --select from tblA and the view
WHERE tblA.colA <> 'XYZ'
return
end>I understand that you can not use GetDate() in a UDF.
? This works on my 2005 server:
CREATE FUNCTION dbo.Func1 ()
RETURNS datetime
AS
BEGIN
return getdate()
END
GO
select dbo.Func1()
William|||We are using SQL2000, and unfortunately it does not work there.
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:O97ruV0BGHA.1032@.TK2MSFTNGP11.phx.gbl...
> ? This works on my 2005 server:
> CREATE FUNCTION dbo.Func1 ()
> RETURNS datetime
> AS
> BEGIN
> return getdate()
> END
> GO
> select dbo.Func1()
> --
> William
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> ? This works on my 2005 server:
> CREATE FUNCTION dbo.Func1 ()
> RETURNS datetime
> AS
> BEGIN
> return getdate()
> END
> GO
> select dbo.Func1()
Yes, but it does not work on SQL 2000.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||yes - that should be fine
not much else to do in this case, though [unless you're using sql2005,
which allows non-deterministic functions in UDFs]
fniles wrote:
> I understand that you can not use GetDate() in a UDF.
> I also can not pass a parameter to the UDF (because this is an Access to S
QL
> Server conversion, and the program that calls this UDF does not pass any
> parameter to it).
> So, I am trying to create a View for GetDate() like the codes below.
> The issue is, I also need to select from another table (tblA) besides
> getting the GetDate() value.
> Is the following codes correct and efficient on how to do that ?
> Thanks.
> create view get_date
> as
> select getdate()dt
> CREATE function dbo.udftemp()
> returns @.myTable TABLE(id varchar(10),datex datetime)
> AS BEGIN
> INSERT INTO @.myTable(id,datex)
> select tblA.id,dt
> FROM tblA, get_date --select from tblA and the view
> WHERE tblA.colA <> 'XYZ'
> return
> end
>|||If it is a migration, then why not migrate to 2005 instead of 2000? Just
curious.
William Stacey [MVP]
"fniles" <fniles@.pfmail.com> wrote in message
news:e%23lFUa0BGHA.2644@.TK2MSFTNGP09.phx.gbl...
> We are using SQL2000, and unfortunately it does not work there.
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:O97ruV0BGHA.1032@.TK2MSFTNGP11.phx.gbl...
>|||He never said anything about 2000, so I gave it a shot.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9734F019C8C9DYazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> Yes, but it does not work on SQL 2000.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your reply.
Is SQL 2005 still a beta product or is it a release product ?
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OynIJo0BGHA.228@.TK2MSFTNGP12.phx.gbl...
> If it is a migration, then why not migrate to 2005 instead of 2000? Just
> curious.
> --
> William Stacey [MVP]
> "fniles" <fniles@.pfmail.com> wrote in message
> news:e%23lFUa0BGHA.2644@.TK2MSFTNGP09.phx.gbl...
>|||RTM. You can get Sql Express 2005 for free currently and buy the Std and
Enterprise versions as normal.
http://www.microsoft.com/sql/default.mspx
William Stacey [MVP]
"fniles" <fniles@.pfmail.com> wrote in message
news:uOzdgy8BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your reply.
> Is SQL 2005 still a beta product or is it a release product ?
>
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:OynIJo0BGHA.228@.TK2MSFTNGP12.phx.gbl...
>|||was it the RTM? or a previous CTP release?
i created a function exactly like in RTM developer edition and it worked
fine.
i don't think edition should matter - but release might.
fniles wrote:
> I just install SQL 2005 Standard Edition, and try the GetDate function
> again, but it still gives me the same error.
> Here is my function:
> CREATE function dbo.udftemp()
> returns @.myTable TABLE(datex datetime)
> AS BEGIN
> INSERT INTO @.myTable(datex)
> select getdate()
> return
> end
> The error I got was: "Invalid use of 'getdate' within a function."
> Do I need SQL 2005 Enterprise Edition for the GetDate() to work ?
> Thanks.
>
> "Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
> news:uPy03j0BGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
>
>