GETDATE

GETDATE

Post by Bria » Thu, 19 Jun 2003 15:54:21



I need to acquire the system date from within a function.
Below is is my function with different things I have
tried, and the server error message.  Does anyone know how
I might do this?

CREATE function dbo.TMW_GETDATE_FUNCTION()
returns datetime
--returns int
AS
BEGIN



usr_userid = SYSTEM_USER

/*      Server: Msg 557, Level 16, State 2, Procedure
TMW_GETSYSTEMDATE, Line 10
        Only functions and extended stored procedures can
be executed from within a
        function.
*/

/*      Server: Msg 443, Level 16, State 1, Procedure
TMW_GETDATE_FUNCTION, Line 14
        Invalid use of 'getdate' within a function.
        Server: Msg 208, Level 16, State 11, Line 2
        Invalid object name 'dbo.TMW_GETDATE_FUNCTION'.
*/

/*      Server: Msg 443, Level 16, State 1, Procedure
TMW_GETDATE_FUNCTION, Line 23
        Invalid use of 'getdate' within a function.
*/



 END

 
 
 

GETDATE

Post by Aaron Bertrand - MV » Thu, 19 Jun 2003 16:00:55


http://www.aspfaq.com/2439


> I need to acquire the system date from within a function.
> Below is is my function with different things I have
> tried, and the server error message.  Does anyone know how
> I might do this?

> CREATE function dbo.TMW_GETDATE_FUNCTION()
> returns datetime
> --returns int
> AS
> BEGIN



> usr_userid = SYSTEM_USER

> /* Server: Msg 557, Level 16, State 2, Procedure
> TMW_GETSYSTEMDATE, Line 10
> Only functions and extended stored procedures can
> be executed from within a
> function.
> */

> /* Server: Msg 443, Level 16, State 1, Procedure
> TMW_GETDATE_FUNCTION, Line 14
> Invalid use of 'getdate' within a function.
> Server: Msg 208, Level 16, State 11, Line 2
> Invalid object name 'dbo.TMW_GETDATE_FUNCTION'.
> */

> /* Server: Msg 443, Level 16, State 1, Procedure
> TMW_GETDATE_FUNCTION, Line 23
> Invalid use of 'getdate' within a function.
> */



>  END


 
 
 

GETDATE

Post by Tom Morea » Thu, 19 Jun 2003 16:01:04


Try:

create view SystemDateTime
as
select getdate () SystemDateTime
go

create function dbo.TMW_GETDATE_FUNCTION()
returns datetime
as
begin
return (select SystemDateTime from SystemDateTime)
end
go

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

I need to acquire the system date from within a function.
Below is is my function with different things I have
tried, and the server error message.  Does anyone know how
I might do this?

CREATE function dbo.TMW_GETDATE_FUNCTION()
returns datetime
--returns int
AS
BEGIN



usr_userid = SYSTEM_USER

/* Server: Msg 557, Level 16, State 2, Procedure
TMW_GETSYSTEMDATE, Line 10
Only functions and extended stored procedures can
be executed from within a
function.
*/

/* Server: Msg 443, Level 16, State 1, Procedure
TMW_GETDATE_FUNCTION, Line 14
Invalid use of 'getdate' within a function.
Server: Msg 208, Level 16, State 11, Line 2
Invalid object name 'dbo.TMW_GETDATE_FUNCTION'.
*/

/* Server: Msg 443, Level 16, State 1, Procedure
TMW_GETDATE_FUNCTION, Line 23
Invalid use of 'getdate' within a function.
*/



 END

 
 
 

GETDATE

Post by Russell Field » Thu, 19 Jun 2003 16:02:25


Brian,

GETDATE is non-deterministic and is forbidden in a function.

As always, there are workarounds.  You can keep a table that has the system
date in it and join to that table. (Then you must maintain that Today table,
of course.)

But, for a more direct workaround do something like this:

CREATE VIEW dbo.Today
AS
SELECT     GETDATE() AS RightNow

Then select from the Today view within your function.

Russell Fields


> I need to acquire the system date from within a function.
> Below is is my function with different things I have
> tried, and the server error message.  Does anyone know how
> I might do this?

> CREATE function dbo.TMW_GETDATE_FUNCTION()
> returns datetime
> --returns int
> AS
> BEGIN



> usr_userid = SYSTEM_USER

> /* Server: Msg 557, Level 16, State 2, Procedure
> TMW_GETSYSTEMDATE, Line 10
> Only functions and extended stored procedures can
> be executed from within a
> function.
> */

> /* Server: Msg 443, Level 16, State 1, Procedure
> TMW_GETDATE_FUNCTION, Line 14
> Invalid use of 'getdate' within a function.
> Server: Msg 208, Level 16, State 11, Line 2
> Invalid object name 'dbo.TMW_GETDATE_FUNCTION'.
> */

> /* Server: Msg 443, Level 16, State 1, Procedure
> TMW_GETDATE_FUNCTION, Line 23
> Invalid use of 'getdate' within a function.
> */



>  END

 
 
 

GETDATE

Post by Aaron Bertrand - MV » Thu, 19 Jun 2003 16:10:51


Vishal, can you fix your system clock?  It thinks it is 2006...


 
 
 

GETDATE

Post by Vishal Parka » Fri, 20 Jun 2003 04:59:23


I do:-)

--
--Vishal



> Vishal, can you fix your system clock?  It thinks it is 2006...




 
 
 

GETDATE

Post by bria » Thu, 19 Jun 2003 16:41:36


For those that are curious, I found the answer on another
website.  Here is the answer.

Create a view, and call this view from your UDF:

This is the View:
CREATE VIEW dbo.vwGetDate
AS
SELECT     GETDATE() AS MyGetDate

This is the UDF:
CREATE FUNCTION [dbo].[TestGetDate] ()  
RETURNS CHAR(10) AS  
BEGIN






END

>-----Original Message-----
>I need to acquire the system date from within a function.
>Below is is my function with different things I have
>tried, and the server error message.  Does anyone know
how
>I might do this?

>CREATE function dbo.TMW_GETDATE_FUNCTION()
>returns datetime
>--returns int
>AS
>BEGIN



>usr_userid = SYSTEM_USER

>/*  Server: Msg 557, Level 16, State 2, Procedure
>TMW_GETSYSTEMDATE, Line 10
>    Only functions and extended stored procedures can
>be executed from within a
>    function.
>*/

>/*  Server: Msg 443, Level 16, State 1, Procedure
>TMW_GETDATE_FUNCTION, Line 14
>    Invalid use of 'getdate' within a function.
>    Server: Msg 208, Level 16, State 11, Line 2
>    Invalid object name 'dbo.TMW_GETDATE_FUNCTION'.
>*/

>/*  Server: Msg 443, Level 16, State 1, Procedure
>TMW_GETDATE_FUNCTION, Line 23
>    Invalid use of 'getdate' within a function.
>*/



> END
>.

 
 
 

GETDATE

Post by Tom Morea » Thu, 19 Jun 2003 17:07:09


Wish I'd have said that ... ;-)

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

For those that are curious, I found the answer on another
website.  Here is the answer.

Create a view, and call this view from your UDF:

This is the View:
CREATE VIEW dbo.vwGetDate
AS
SELECT     GETDATE() AS MyGetDate

This is the UDF:
CREATE FUNCTION [dbo].[TestGetDate] ()  
RETURNS CHAR(10) AS  
BEGIN






END

>-----Original Message-----
>I need to acquire the system date from within a function.
>Below is is my function with different things I have
>tried, and the server error message.  Does anyone know
how
>I might do this?

>CREATE function dbo.TMW_GETDATE_FUNCTION()
>returns datetime
>--returns int
>AS
>BEGIN



>usr_userid = SYSTEM_USER

>/* Server: Msg 557, Level 16, State 2, Procedure
>TMW_GETSYSTEMDATE, Line 10
> Only functions and extended stored procedures can
>be executed from within a
> function.
>*/

>/* Server: Msg 443, Level 16, State 1, Procedure
>TMW_GETDATE_FUNCTION, Line 14
> Invalid use of 'getdate' within a function.
> Server: Msg 208, Level 16, State 11, Line 2
> Invalid object name 'dbo.TMW_GETDATE_FUNCTION'.
>*/

>/* Server: Msg 443, Level 16, State 1, Procedure
>TMW_GETDATE_FUNCTION, Line 23
> Invalid use of 'getdate' within a function.
>*/



> END
>.

 
 
 

GETDATE

Post by Kalen Delane » Thu, 19 Jun 2003 17:09:50


Can you please reference the website where you found this?

Thanks

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


> For those that are curious, I found the answer on another
> website.  Here is the answer.

> Create a view, and call this view from your UDF:

> This is the View:
> CREATE VIEW dbo.vwGetDate
> AS
> SELECT     GETDATE() AS MyGetDate

> This is the UDF:
> CREATE FUNCTION [dbo].[TestGetDate] ()
> RETURNS CHAR(10) AS
> BEGIN






> END
> >-----Original Message-----
> >I need to acquire the system date from within a function.
> >Below is is my function with different things I have
> >tried, and the server error message.  Does anyone know
> how
> >I might do this?

> >CREATE function dbo.TMW_GETDATE_FUNCTION()
> >returns datetime
> >--returns int
> >AS
> >BEGIN



> >usr_userid = SYSTEM_USER

> >/* Server: Msg 557, Level 16, State 2, Procedure
> >TMW_GETSYSTEMDATE, Line 10
> > Only functions and extended stored procedures can
> >be executed from within a
> > function.
> >*/

> >/* Server: Msg 443, Level 16, State 1, Procedure
> >TMW_GETDATE_FUNCTION, Line 14
> > Invalid use of 'getdate' within a function.
> > Server: Msg 208, Level 16, State 11, Line 2
> > Invalid object name 'dbo.TMW_GETDATE_FUNCTION'.
> >*/

> >/* Server: Msg 443, Level 16, State 1, Procedure
> >TMW_GETDATE_FUNCTION, Line 23
> > Invalid use of 'getdate' within a function.
> >*/



> > END
> >.

 
 
 

GETDATE

Post by John Bel » Thu, 19 Jun 2003 18:08:30


Looks like on of Aaron's

http://www.aspfaq.com/show.asp?id=2439

John


> Can you please reference the website where you found this?

> Thanks

> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com



> > For those that are curious, I found the answer on another
> > website.  Here is the answer.

> > Create a view, and call this view from your UDF:

> > This is the View:
> > CREATE VIEW dbo.vwGetDate
> > AS
> > SELECT     GETDATE() AS MyGetDate

> > This is the UDF:
> > CREATE FUNCTION [dbo].[TestGetDate] ()
> > RETURNS CHAR(10) AS
> > BEGIN






> > END
> > >-----Original Message-----
> > >I need to acquire the system date from within a function.
> > >Below is is my function with different things I have
> > >tried, and the server error message.  Does anyone know
> > how
> > >I might do this?

> > >CREATE function dbo.TMW_GETDATE_FUNCTION()
> > >returns datetime
> > >--returns int
> > >AS
> > >BEGIN



> > >usr_userid = SYSTEM_USER

> > >/* Server: Msg 557, Level 16, State 2, Procedure
> > >TMW_GETSYSTEMDATE, Line 10
> > > Only functions and extended stored procedures can
> > >be executed from within a
> > > function.
> > >*/

> > >/* Server: Msg 443, Level 16, State 1, Procedure
> > >TMW_GETDATE_FUNCTION, Line 14
> > > Invalid use of 'getdate' within a function.
> > > Server: Msg 208, Level 16, State 11, Line 2
> > > Invalid object name 'dbo.TMW_GETDATE_FUNCTION'.
> > >*/

> > >/* Server: Msg 443, Level 16, State 1, Procedure
> > >TMW_GETDATE_FUNCTION, Line 23
> > > Invalid use of 'getdate' within a function.
> > >*/



> > > END
> > >.

 
 
 

1. GETDATE() problem with smalldatetime (where mydate = getdate()?)

Hi.

I am very new to SQL Server so please bear with me. I am sure it is
version 7.0 though (oh lordie I hear you call cry!).

Anyway...here are my difficulties: I imported a lot of data from
Access to SQL, using the SQL import thing in Enterprise Manager.  One
of my tables relies strongly on, what was, a Date/Time field in Access
which I now notice is a smalldatetime field.

The dates in this field are displayed, in Enterprise Manager anyway,
as dd/mm/yyyy although, through tinkering, I noticed that they are
actually stored in the database as yyyy-mm-dd. (I'm presuming they are
displayed as dd/mm/yyyy according to my regional settings?)

Ok, so I was trying to create a web page that would display any item
that had "today" as the date (changing dynamically as each day passes
of course). I used to do this in VBScript / ASP using the line:
SELECT * FROM tablename WHERE datefield = Date()

(or was it Now() - oh crikey!)

Anyway, it used to work fine like that.  In moving to SQL I decided
that I would just create a view that already ran this query, and then
just have my page say SELECT * FROM todaysitemstable.  This, I felt,
would be the most efficient way of presenting this data (all advice
greatly appreciated!).

I chased up the manual, online support etc and discovered that I
needed to use GETDATE() when trying this same thing in SQL.  I
couldn't (can't!) get this to work though because, I THINK, the
smalldatetime type saves "to the minute", and yet my old data was
simply dd/mm/yyyy.  The upshot being that when I asked for matches for
GetDate() it was saying to me "there are no items that match
yyyy-mm-dd 00:00" (yes, I know, computers can't talk to you!).

Further investigation, and I tried converting mydate and getdate into
varchar types (varchar(10)) like so:

SELECT     *
FROM         dbo.tablename
WHERE     (CONVERT(varchar(10), mydate, 101) = CONVERT(varchar(10),
GETDATE(), 101))

Bingo!! It worked and I was well chuffed. However...I also now need to
add two new items of functionality:

I want to query my table to show me dates that are equal to or greater
than GETDATE().  Tried the logical thing which was to change the = in
the above statement to a >= but it doesn't work.  And I think I know
why, I just don't know what to do now!?

I am presuming that, because I am converting to a varchar value that
this changes the data to a string  - dd/mm/yyyy (eg. 21/03/2003).  So
trying to perform a mathematical command on a string is pretty much
useless?  Is that why it isn't working?  Can someone help in terms of
letting me know what I need to do?

Also, I would like to show items that occur, or have occured in the
current week which, in my mind, sounds complicated.

All help greatly appreciated. Hope to hear from someone...
Regards
Nathon

2. Bug #18326

3. getDate

4. Borland Reflex 2.0 Recover

5. A Useful Date Conversion Function -- Problem Initializing Default Function Value to Getdate()

6. read only problem

7. Getdate() after clock change

8. 4GL: FGL_setcurrline

9. GetDate()

10. GETDATE()

11. GETDATE In UDF

12. How to GetDate() ?

13. Can datepart be a variable like: DateAdd(@uom, 1, GetDate())