Posted by SQL Server on 10/01/03 11:49

I've been working this for a while. Kind of new to SQL Server
functions and not seeing what I am doing wrong. I have this function

CREATE FUNCTION dbo.test (@Group varchar(50))
RETURNS smalldatetime AS
BEGIN
Declare @retVal varchar(10)
(SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@Group))
return convert(smalldatetime, @retVal, 1)
END

The error I get is
Server: Msg 296, Level 16, State 3, Procedure test, Line 6
The conversion of char data type to smalldatetime data type resulted in
an out-of-range smalldatetime value.

1) I tried declaring @retVal as a smalldatetime and get the error "Must
declare the variable '@retVal'.'
2) If I run that same query in query analyzer (manually inserting the
parm) it returns 11/14/2006. That's what I want.

If I change the function to this and run it
CREATE FUNCTION dbo.test (@Group varchar(50))
RETURNS varchar(50) AS
BEGIN
Declare @retVal varchar(50)
(SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@Group))
return convert(smalldatetime, @retVal, 1)
END

It now works but the return value is Nov 14 2006 12:00AM

What am I doing wrong?

TIA

• Next in forum: Parameterized SP in WHERE Clause of Another SP
• Prev in forum: ANN: Advanced Data Generator 1.6.2 released
• Thread view: Question about returning a smalldatetime from a Function

[Reply to this message]