About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Saturday, September 12, 2009

GetDate function used in functions SQL Server 2000/2005     In an answer on LinkedIn one of the users made me attentive that in SQL Server 2005 and later versions the GetDate and other non-deterministic UDFs can be used inside functions, thing not possible under SQL Server 2000. This lack of functionality, cumbersome especially when needed to use current date in table-valued functions, was requesting the date to be sent as parameter.     For exemplification the following piece of code would run successfully on SQL Server 2005 but fail on SQL Server 2000, returning the below error message. CREATE FUNCTION dbo.GetCurrentDate() RETURNS smalldatetime AS BEGIN RETURN (GetDate()) END GO SELECT dbo.GetCurrentDate() Error message: Server: Msg 443, Level 16, State 1, Procedure GetCurrentDate, Line 6 Invalid use of 'getdate' within a function.

No comments: