SQL 2000 Custom Function call GETDATE () times wrong

When GETDATE () is invoked in a custom function in a SQL 2000 database, the result compiles the times incorrectly, prompting errors as follows: Invalid use of ' getdate ' within a function

I'll give you a test example as shown below,

--================================================================================================
--Author : Kerry
--createdate:2011-03-23-
-Description: Automatically generate serial number according to the rules
----------------------------------------- ---------------------------------------------------------
--updatedate:
------------------------------- -------------------------------------------------------------------
createprocedure usp_ Riminsamplenogenerate
@TypeVARCHAR (a)
as
    
SET NOCOUNT on;
    
Declare@samplenoasvarchar (a);
Declare@dateasvarchar (8);
Declare@serialnumberasvarchar (3)
    
    
BEGIN
    
Select@date=convert (VARCHAR), GETDATE (), 112);
--Business rules processing end go

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

If you change the GETDATE () to dbo. GETDATE () compiles without error, but the function is invoked with an error, as follows: Object name ' dbo. GETDATE '

Invalid.

This is because you do not allow built-in nondeterministic functions in the body of a user-defined function (deterministic and nondeterministic functions you can refer to in the MSDN documentation). In SQL 2000 there is

Two solutions: Use the GETDATE () function as an argument to a custom function (as shown below) or define a view that obtains the current time to solve the problem. GETDATE () is not accurate

Fixed function. However, it seems that in SQL 2005, a custom function can call the function getdate (), as shown below, and there will be no errors when compiling and calling.

--==============================================================================================
--Author: Kerry
-createdate:2011-03-23-
Description: Automatically generate serial numbers according to the rules
------------------------------------------ ------------------------------------------------------
--updatedate:
---------------------------------- --------------------------------------------------------------
createfunction[dbo]. [Fun_generateraminisampleno] (@TypeVARCHAR (a), @DateTimeDATETIME)
Returnsvarchar () as
BEGIN
    
Declare@samplenovarchar (a);
Declare@datevarchar (8);
Declare@serialnumbervarchar (3);
    
Select@date=convert (VARCHAR (8), GETDATE (), 112); --Compilation error
--select @Date = CONVERT (VARCHAR (8), dbo. GETDATE (), 112); --Call error
--select @Date = CONVERT (VARCHAR (8), @DateTime, 112);
--Business rule end go

Author: The Hermit of Xiaoxiang

Source: http://www.cnblogs.com/kerrycode/