Failure Details: udf

Showing posts with label udf. Show all posts

Friday, March 23, 2012

GetDate() in User Defined Functions, Parameters in View

1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus

> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.

> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields

Getdate() in UDF column workaround

I have a column that needs to display the number of Status hours between
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usin
g
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking mayb
e
the text box record source could be a select statement but not sure how to
write it, any ideas?DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())
Roy
On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkAlan@.discussions.microsoft.com> wrote:

>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usi
ng
>an Access project as my front end and SQL Server 2000 as my back end. I hav
e
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking may
be
>the text box record source could be a select statement but not sure how to
>write it, any ideas?|||Hi
CREATE FUNCTION dbo.Get_Getdate
(@.dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@.microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?sql

getdate() in UDF

I am trying to use getdate() function inside of my UDF function.
I am getting an error message about invalid use of getdate() function.
Is there a way to use functions in UDF functions?http://www.aspfaq.com/2439
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%23vVy542PFHA.2468@.tk2msftngp13.phx.gbl...
>I am trying to use getdate() function inside of my UDF function.
> I am getting an error message about invalid use of getdate() function.
> Is there a way to use functions in UDF functions?
>

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

Wednesday, March 21, 2012

GetDate() as parameter to UDF

I understand that GetDate() cannot be used within the *body* of a
user-defined function because it is non-deterministic.
I now have a table-valued function with which I want to use DateAdd and
GetDate() as *parameters* -- thus:
Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
I get "Incorrect syntax near '('.
The same happens if I eliminate the DateAdd. If I use hard-coded dates,
e.g. '1/1/2006', the function works as expected.
What am I doing wrong?
Thanks.
Daniel WilsonDaniel Wilson a crit :
> I understand that GetDate() cannot be used within the *body* of a
> user-defined function because it is non-deterministic.
> I now have a table-valued function with which I want to use DateAdd and
> GetDate() as *parameters* -- thus:
> Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
Use a view instead...
here is an exemple :
CREATE VIEW dbo.V_DATE_TIME_DTM
AS
SELECT CURRENT_TIMESTAMP AS DTM_TIMESTAMP
GO
CREATE FUNCTION dbo.F_CURRENT_DATE ()
RETURNS DATETIME
AS
BEGIN
-- date/time with time to ZERO
DECLARE @.D DATETIME
SELECT @.D = dbo.F_DATEONLY(DAT_DATE)
FROM dbo.V_DATE_DAT
RETURN @.D
END
GO
A +

> I get "Incorrect syntax near '('.
> The same happens if I eliminate the DateAdd. If I use hard-coded dates,
> e.g. '1/1/2006', the function works as expected.
> What am I doing wrong?
> Thanks.
> Daniel Wilson
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Declare variables and set their values and pass them to the function:
declare @.dt datetime
set @.dt = getdate()
select * from dbo.myfn(@.dt)
"Daniel Wilson" wrote:

> I understand that GetDate() cannot be used within the *body* of a
> user-defined function because it is non-deterministic.
> I now have a table-valued function with which I want to use DateAdd and
> GetDate() as *parameters* -- thus:
> Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
> I get "Incorrect syntax near '('.
> The same happens if I eliminate the DateAdd. If I use hard-coded dates,
> e.g. '1/1/2006', the function works as expected.
> What am I doing wrong?
> Thanks.
> Daniel Wilson
>
>|||Thank you, both.
We're working with the view solution b/c we're using the function within a
view.
dwilson
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ee4GCboUGHA.4792@.TK2MSFTNGP14.phx.gbl...
> Daniel Wilson a crit :
> Use a view instead...
> here is an exemple :
> CREATE VIEW dbo.V_DATE_TIME_DTM
> AS
> SELECT CURRENT_TIMESTAMP AS DTM_TIMESTAMP
> GO
>
> CREATE FUNCTION dbo.F_CURRENT_DATE ()
> RETURNS DATETIME
> AS
> BEGIN
> -- date/time with time to ZERO
> DECLARE @.D DATETIME
> SELECT @.D = dbo.F_DATEONLY(DAT_DATE)
> FROM dbo.V_DATE_DAT
> RETURN @.D
> END
> GO
>
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

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

GETDATE in a function

Hi
Yes , you cannot , however you are be able to pass GETDATE() function as a
parameter onto the UDF and later to operate with it

> I'm trying to make three functions, Today, Yesterday and Tomorrow
SELECT
DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
DATEADD (d,DATEDIFF(D,0,GETDATE()),0) AS Today,
DATEADD (d,DATEDIFF(D,0,GETDATE())+1,0) AS Tomorrow
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7B10973B-0A65-4E45-848C-441ADB34E8F0@.microsoft.com...
> I'm trying to make three functions, Today, Yesterday and Tomorrow. But I
> can't, because you can't put GETDATE in a function.
> 1) Why not?
> 2) What can I do to make this work?
> Maury"Uri Dimant" wrote:

> Yes , you cannot
Any idea why? I can't seem to think of any good reason for this.

> however you are be able to pass GETDATE() function as a
> parameter onto the UDF and later to operate with it
UDF?

> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
Oh, I know how to do it mechanically, but it's precisely all that syntax
that I'm trying to avoid!
Maury|||Maury Markowitz wrote:
> "Uri Dimant" wrote:
>
> Any idea why? I can't seem to think of any good reason for this.
>
> UDF?
>
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>
Read this, it will explain all:
http://msdn.microsoft.com/msdnmag/i.../11/DataPoints/|||When a function is executed, it has to know exactly what data it will
operate on. Getdate() is a 'non-determinate' function since every time it
operates, it produces a different value.
'UDF' = user defined function. A prefix that some use in naming functions.
Also in popular usage is 'fn'.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:15F3B36E-49DD-4539-8B24-1DBE13AA3CC7@.microsoft.com...
> "Uri Dimant" wrote:
>
> Any idea why? I can't seem to think of any good reason for this.
>
> UDF?
>
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>|||"Arnie Rowland" wrote:

> When a function is executed, it has to know exactly what data it will
> operate on. Getdate() is a 'non-determinate' function since every time it
> operates, it produces a different value.
This doesn't really tell me WHY though. WHY does returning a different
datetime make it impossible to use in a function?
Consider this:
ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
BEGIN
RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
'/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar, DAY(@.date))))
END
This would be called like...
SELECT dbo.StartOfDay(GETDATE())
Now what is it about exactly that precudes this solution:
ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
BEGIN
SELECT @.date = GETDATE()
RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
'/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar, DAY(@.date))))
END
The end result is exactly the same. The code that needs to run is exactly
the same. The actual execution is almost identical. So what is it about the
function creation mechanism inside SQL Server that makes this illegal?
Maury|||In your first example, the function is NOT passed in the function getdate(),
is it passed in the current returned value from getdate() -so therefore, the
function knows exactly the values it has to operate with.
In your second example, the function would not know the value of getdate()
until after it starts working -and that is, by design, not allowed.
Why does 0/1 create such a turmoil in math. I don't know 'exactly, but I
accept the it just isn't allowed.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14D32701-04DF-4A86-B680-6C1669715DEF@.microsoft.com...
> "Arnie Rowland" wrote:
>
> This doesn't really tell me WHY though. WHY does returning a different
> datetime make it impossible to use in a function?
> Consider this:
> ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
> BEGIN
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> This would be called like...
> SELECT dbo.StartOfDay(GETDATE())
> Now what is it about exactly that precudes this solution:
> ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
> BEGIN
> SELECT @.date = GETDATE()
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> The end result is exactly the same. The code that needs to run is exactly
> the same. The actual execution is almost identical. So what is it about
> the
> function creation mechanism inside SQL Server that makes this illegal?
> Maury|||User-defined functions are by definition "deterministic". A deterministic
function is one in which, for every value (or set of values) you pass into
it, it returns the same result. Examples of deterministic functions include
sine(), cosine(), etc.
Because of this limitation on determinism in user-defined functions, you are
not allowed to use non-deterministic functions inside your UDF's (like rand
or getdate). There are ways around this limitation, like creating a VIEW
composed of SELECT GETDATE(); and selecting from the VIEW from within your
UDF.
MS warns against relying on UDF's that circumvent determinism like this and
don't return the same results for the same set of values every time,
however.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14D32701-04DF-4A86-B680-6C1669715DEF@.microsoft.com...
> "Arnie Rowland" wrote:
>
> This doesn't really tell me WHY though. WHY does returning a different
> datetime make it impossible to use in a function?
> Consider this:
> ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
> BEGIN
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> This would be called like...
> SELECT dbo.StartOfDay(GETDATE())
> Now what is it about exactly that precudes this solution:
> ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
> BEGIN
> SELECT @.date = GETDATE()
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> The end result is exactly the same. The code that needs to run is exactly
> the same. The actual execution is almost identical. So what is it about
> the
> function creation mechanism inside SQL Server that makes this illegal?
> Maury|||I'm trying to make three functions, Today, Yesterday and Tomorrow. But I
can't, because you can't put GETDATE in a function.
1) Why not?
2) What can I do to make this work?
Maury|||Maury Markowitz wrote:
> "Uri Dimant" wrote:
>
> Any idea why? I can't seem to think of any good reason for this.
>
> UDF?
>
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>
Read this, it will explain all:
http://msdn.microsoft.com/msdnmag/i.../11/DataPoints/|||When a function is executed, it has to know exactly what data it will
operate on. Getdate() is a 'non-determinate' function since every time it
operates, it produces a different value.
'UDF' = user defined function. A prefix that some use in naming functions.
Also in popular usage is 'fn'.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:15F3B36E-49DD-4539-8B24-1DBE13AA3CC7@.microsoft.com...
> "Uri Dimant" wrote:
>
> Any idea why? I can't seem to think of any good reason for this.
>
> UDF?
>
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>

GetDate as Parameter for UDF Function returns table

Help!!
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.Simply CAST it as a varchar..
....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo.hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > Help!!
> >
> > I am trying to pass GetDate() as a paramter into a function that returns
a
> > table.
> >
> > select * from
> > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> GetDate(),'10/30/2004','Company')
> >
> > This fails but it works if I pass in the date as a string. I need to
use
> > GetDate.
> >
> > I cannot create a local variable, this is a view.
>|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > Simply CAST it as a varchar..
> >
> > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> >
> > Note,
> >
> > You may wish to parse it out to get only the portions of the date that
you
> > want...
> >
> > You could try casting it as a decimal as well. That may work better for
> > you.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
> > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > Help!!
> > >
> > > I am trying to pass GetDate() as a paramter into a function that
returns
> a
> > > table.
> > >
> > > select * from
> > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > GetDate(),'10/30/2004','Company')
> > >
> > > This fails but it works if I pass in the date as a string. I need to
> use
> > > GetDate.
> > >
> > > I cannot create a local variable, this is a view.
> >
> >
>|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> > Rick
> > Your suggestion does not work
> >
> > CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> > RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> > AS
> > BEGIN
> > INSERT INTO @.Dates VALUES(@.dt)
> > RETURN
> > END
> >
> > --Doesnt work (as you suggested)
> > SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> > --Does work
> > DECLARE @.dt DATETIME
> > SET @.dt=GETDATE()
> > SELECT * from dbo.fn_dates (@.dt)
> >
> >
> >
> > "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> > news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > > Simply CAST it as a varchar..
> > >
> > > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> > >
> > > Note,
> > >
> > > You may wish to parse it out to get only the portions of the date that
> you
> > > want...
> > >
> > > You could try casting it as a decimal as well. That may work better
for
> > > you.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> > >
> > > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > > Help!!
> > > >
> > > > I am trying to pass GetDate() as a paramter into a function that
> returns
> > a
> > > > table.
> > > >
> > > > select * from
> > > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > > GetDate(),'10/30/2004','Company')
> > > >
> > > > This fails but it works if I pass in the date as a string. I need
to
> > use
> > > > GetDate.
> > > >
> > > > I cannot create a local variable, this is a view.
> > >
> > >
> >
> >
>|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>sql

Friday, March 23, 2012

GetDate() in User Defined Functions, Parameters in View

Getdate() in UDF column workaround

getdate() in UDF

GetDate() in UDF

Wednesday, March 21, 2012

GetDate() as parameter to UDF

getdate in UDF

GETDATE in a function

GetDate as Parameter for UDF Function returns table

GetDate as Parameter for UDF Function returns table

GetDate as Parameter for UDF Function returns table

Failure Details

Blog Archive

About Me