SQL Upsizing: Invalid use of "getdate" within a function

[el zorro]

I am in the process of upsizing a database with about 125 queries. Many of
them have failed to upsize due to "Server Error 443: Invalid use of
'getdate' within a function."

As far as I can tell, this is due to a criteria I have entered in a date
field of the query as "Between Date () -30 And Date () + 30." During the
upsizing process to SQL, the "wizard" apparently subsitutes "getdate" for
"Date ()," but then can't finish the conversion. So I guess I need to modify
the query. (The idea is to get all records with dates within a 60 day window,
30 back from today and 30 forward.)

Any thoughts will be greatly appreciated. Thanks!

 

[KARL DEWEY]

Try using SysDate.

 

[Sylvain Lafontaine]

What tool did you use to make the upsizing and to what type of file
(MDB/ACCDB with ODBC linked tables or ADP project) are you upsizing?

The correct substitution for Date() should be GetDate(); however, GetDate()
can only be used inside a stored procedure (SP) and not inside an User
Defined Function (UDF) because all UDF must (should) be deterministic and
GetDate() is not. You can bypass the problem by hiding GetDate() inside a
View:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=715&lngWId=5

http://novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-2-udf_DT_CurrTime.htm

but an easier solution might be to do the conversion yourself and use SP
instead of UDF.

An easy way to do this would be to collect all querydef into one big file by
using the following script from Brendan Reynolds and use your favorite word
processor to translate these queries to T-SQL:

Public Sub WriteQueries()



Dim intFile As Integer

Dim db As DAO.Database

Dim qdfs As DAO.QueryDefs

Dim qdf As DAO.QueryDef



intFile = FreeFile

Open CurrentProject.Path & "\queries.text" For Output As intFile

Set db = CurrentDb

Set qdfs = db.QueryDefs


For Each qdf In qdfs

Print #intFile, qdf.Name

Print #intFile, qdf.SQL

Next qdf

Close intFile
End Sub

 

[Sylvain Lafontaine]

Addendum: it seems that the use of a View for hiding the GetDate() function
is not the best way to do this and that it will be a better idea to use an
OpenQuery, see:

http://sqlserver2000.databases.aspf...tdate-within-a-user-defined-function-udf.html

 

[el zorro]

THanks Sylvain-- I am just using the upsizing Wizard in Access to upsize a
split mde to SQL Server. (It's in Access 2000 format, which I can upgrade to
Access 2002 if that will make any difference.)

I notice that some of the queries are upsized as VIEWS, and the Date ()
function is succesfully upsized to getdate in those. But, for no reason I can
discern, some of the queries are upsized as FUNCTIONS, and those don't like
the date function at all. Ideally, I would find a way to rewrite the failed
queries in Access so that they would upsize successfully, but I may have to
make the corrections on the SQL side, following ideas you have given me.