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

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!
0
Utf
11/28/2007 12:04:05 AM
access 16762 articles. 3 followers. Follow

4 Replies
965 Views

Similar Articles

[PageSpeed] 26

Try using SysDate.
-- 
KARL DEWEY
Build a little - Test a little


"el zorro" wrote:

> 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!
0
Utf
11/28/2007 12:16:01 AM
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, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"el zorro" <elzorro@discussions.microsoft.com> wrote in message 
news:8CA822EA-1DAD-44BE-9296-CE18E0142321@microsoft.com...
>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! 


0
Sylvain
11/28/2007 1:19:08 AM
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.aspfaq.com/how-do-i-use-getdate-within-a-user-defined-function-udf.html

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
wrote in message news:e6m1vyVMIHA.4912@TK2MSFTNGP06.phx.gbl...
> 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, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "el zorro" <elzorro@discussions.microsoft.com> wrote in message 
> news:8CA822EA-1DAD-44BE-9296-CE18E0142321@microsoft.com...
>>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!
>
> 


0
Sylvain
11/28/2007 1:23:48 AM
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.

"Sylvain Lafontaine" wrote:

> 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.aspfaq.com/how-do-i-use-getdate-within-a-user-defined-function-udf.html
> 
> -- 
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> 
> 
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
> wrote in message news:e6m1vyVMIHA.4912@TK2MSFTNGP06.phx.gbl...
> > 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, ing.
> > MVP - Technologies Virtual-PC
> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >
> >
> > "el zorro" <elzorro@discussions.microsoft.com> wrote in message 
> > news:8CA822EA-1DAD-44BE-9296-CE18E0142321@microsoft.com...
> >>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!
> >
> > 
> 
> 
> 
0
Utf
12/4/2007 10:06:03 PM
Reply:

Similar Artilces:

Getdate to go back in time
Hello, I am not a t-sql programmer but I need to change a query that gets data every day to go back in time to get days that were missed. The current query is below. How do I change the getdate function to NOT JUST GO BACKWARD TO A PARTICULAR DAY but get ALL the data from the missed days at once. TIA!!!, JJ SELECT .... FROM .... WHERE trx.ibin = hst.ibin AND drs.num = trx.usr AND convert(char(8), trx.trxdate, 112) = convert(char(8), GetDate(), 112) On Fri, 19 Feb 2010 13:02:02 -0800, jj <jj@discussions.microsoft.com> wrote: >Hello, >I am not a t-sql pro...

SQL Upsizing: Invalid use of "getdate" within a function
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 withi...