UDF and Table Column Formula
-
We have a UDF (defined below that we are rtying to call from a column in the databse table. We are using the formua field in SQL enterprise manager to enter the call to the UDF. Both columns are defined as datetime as well as the column we are trying to get the UDF to work in. I have tried to use the following: database.dbo.RuntimeSort(database.dbo.table.columnname, database.dbo.table.columnname) RuntimeSort(columnname,columnname) database.dbo.RuntimeSort(columnname, columnname) RuntimeSort(database.dbo.table.columnname, database.dbo.table.columnname) UDF: CREATE FUNCTION dbo.RuntimeSort (@DateProduction datetime, @TimeStart datetime) RETURNS datetime BEGIN DECLARE @Date datetime DECLARE @DateReturn datetime SET @Date = dateadd(day, 1, @DateProduction) SET @DateReturn = datepart(mm, @Date) + '/' + datepart(dd, @Date) + '/' + datepart(yyyy, @Date) + ' ' + datepart(hour, @TimeStart) + ':' + datepart(minute, @TimeStart) + ':' + datepart(second, @TimeStart) RETURN @DateReturn END I am at a loss here :confused::confused::confused: and any help would be appreciated. Regards Eric C. Tomlinson
No comment, Mr. Senator
-
We have a UDF (defined below that we are rtying to call from a column in the databse table. We are using the formua field in SQL enterprise manager to enter the call to the UDF. Both columns are defined as datetime as well as the column we are trying to get the UDF to work in. I have tried to use the following: database.dbo.RuntimeSort(database.dbo.table.columnname, database.dbo.table.columnname) RuntimeSort(columnname,columnname) database.dbo.RuntimeSort(columnname, columnname) RuntimeSort(database.dbo.table.columnname, database.dbo.table.columnname) UDF: CREATE FUNCTION dbo.RuntimeSort (@DateProduction datetime, @TimeStart datetime) RETURNS datetime BEGIN DECLARE @Date datetime DECLARE @DateReturn datetime SET @Date = dateadd(day, 1, @DateProduction) SET @DateReturn = datepart(mm, @Date) + '/' + datepart(dd, @Date) + '/' + datepart(yyyy, @Date) + ' ' + datepart(hour, @TimeStart) + ':' + datepart(minute, @TimeStart) + ':' + datepart(second, @TimeStart) RETURN @DateReturn END I am at a loss here :confused::confused::confused: and any help would be appreciated. Regards Eric C. Tomlinson
No comment, Mr. Senator
If that function is a direct cut-and-paste from Enterprise manager, there's a bug in it: "Server: Msg 245, Level 16, State 1, Procedure RuntimeSort, Line 10 Syntax error converting the varchar value '/' to a column of data type int." **Edit** I see the problem. You have
DATEPART() + '/'DATEPARTreturns an int, which you are NOT casting to a varchar. So, when you try to add '/' to an int, it tries to auto-cast the slash into an int, and the function bombs. To answer your original question, however, the proper method of defining the formula isdbo.RuntimeSort(date1, date2). You MUST provide the object owner, as well as the object name, and ALL parameters. UDF's on SQL Server 2000 don't support default parameter values like stored procs do.
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB