Using GetDate() in a User Defined Function

Question

Using GetDate() in a User Defined Function

Alan Campin

SSC Enthusiast

Points: 182
More actions
November 10, 2004 at 3:53 pm

#63558

I took some code that used GetDate in a stored procedure and attempted to convert to a User Defined Function returning a table but it keeps failing to compile with a error 443 - Invalid use of GetDate in User Defined Function. Anybody got any idea why you could not use a GetDate in a UDF? This is the UDF.
CREATE Function aa_fn_GetOrganizationValues
                 ( @OrganizationType int )
Returns Table
As
Return (
     Select T01.PositionIdNo            as PositionIdNo,
               T04.OrganizationTypeIdNo    as Type,
               T04.OrganizationCode        as OrgCode,
               T04.OrganizationDescription as OrgDesc
      From ORGANIZATION_Curr T01
                  Inner Join vPOSITION_CODES T02
                    On T01.PositionIdNo = T02.PositionIdNo
                  Inner Join vPOSITION_ORGS T03
                     On T01.PositionIdNo = T03.PositionIdNo
                  Inner Join vORGANIZATIONS T04
                     On T03.OrgCodeIdNo = T04.OrgCodeIdNo
       Where   (T04.OrganizationTypeIdNo = @OrganizationType) and
                  (GetDate() Between T02.PositionCodeFromEffectDate AND T02.PositionCodeToEffectDate) and
                  (GetDate() Between T03.PositionOrgFromEffectDate AND T03.PositionOrgToEffectDate) and
                (GetDate() Between T04.OrganizationFromEffectDate AND T04.OrganizationToEffectDate)
       )

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Here's a quote from BOL: "Built-in nondeterministic functions are not allowed in the body of user-defined functions." Getdate() is, of course, non-deterministic, as it "may return different values when called with the same input parameters" (input parameters being effectively NULL in this case).

I haven't tried this, but you might be able to get round it by passing getdate() to the function as an input parameter and using that.

Regards

Phil

Alan Campin SSC Enthusiast Points: 182 More actions · Answer 2

Thanks. That is what I ended up doing. Now the next stupid question. Why can't I use in GetDate() in a call to the function?

UPDATE ORGANIZATION_Curr

SET LocationCode = T02.OrgCode,

Location = T02.OrgDesc

FROM Organization_Curr T01

LEFT OUTER JOIN aa_fn_GetOrganizationValues(106,GetDate()) T02

ON T02.PositionIdNo = T01.PositionIdNo

This does not compile either. Again, nothing I find in the book. I can change it to a @ value but that seems stupid that I have to do that.

jag anegondi Newbie Points: 7 More actions · Answer 3

Create a view on getdate() and use it in the function.

create view v_current_date as select getdate() as [curdate]...

go

CREATE Function aa_fn_GetOrganizationValues

( @OrganizationType int )

Returns Table

As

declare @x datetime

select @x=curdate from v_current_date

Return (..........

(@x Between T02.PositionCodeFromEffectDate AND T02.PositionCodeToEffectDate and ........)

I hope this helps.

Jag

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Create a view on getdate() and use it in the function.

Be careful with this advise!

See http://www.insidesql.de/content/view/100/ on what can happen.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mmatseke Ten Centuries Points: 1129 More actions · Answer 5

You can get arround the limitations of getdate() within UDF by creating a view:

create view dbo.vw_getdate (CurrentDateTime) as select getdate()

select * from dbo.vw_getdate (CurrentDateTime)

go

Then you can use this view within your function, OR create another function that returns datetime by referencing the view:

create function dbo.fn_getdate()

returns datetime

as

begin

return(

select*from dbo.vw_getdate

)

end

go

select dbo.fn_getdate()

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Well, sometimes it is useful to read the thread before posting

See the two postings before yours.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]