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...
>
>
>

GetDate() conversion

Can anyone show me how to return Getdate() as dd/mm/yyyySelect CONVERT(Varchar(10),getdate(),103)
HTH, Jens Suessmeyer.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:1423B0C1-97EF-401C-A007-939C6A86007D@.microsoft.com...
> Can anyone show me how to return Getdate() as dd/mm/yyyy|||Peter
Look at CONVERT system function which has a 'style' parameter in the BOL.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:1423B0C1-97EF-401C-A007-939C6A86007D@.microsoft.com...
> Can anyone show me how to return Getdate() as dd/mm/yyyy|||Go to this website and it will show you how to get the date converted the wa
y
you want it.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5949
JP
"Peter Newman" wrote:

> Can anyone show me how to return Getdate() as dd/mm/yyyysql

get time in SQL server

Hi ,
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgsmax
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1 -- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
>.
>