|
|
Posted by Erland Sommarskog on 10/02/87 11:49
SQL Server (alderran666@gmail.com) writes:
> 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.
What data type is t_master_schedules.date? If it is varchar(10), and
it returns 11/14/2006, the query looks, eh, funny to me. First,
11/14/2006 does not look like a date to me. :-) But even if I assume
that 11 is supposed to be a month, it seems strange that you consider
2006-11-14 to be less than 2004-12-12. Shouldn't your query read
MIN(convert(smalldatetime, [date], 101) in such case?
Alternatively, the column is datetime or smalldatetime, but in such
there is no need to incolve varchar at all.
Anyway, when I try:
select convert(smalldatetime, '11/14/2006', 1)
I get:
Server: Msg 295, Level 16, State 3, Line 1
Syntax error converting character string to smalldatetime data type.
Whereas
select convert(smalldatetime, '11/14/2006', 101)
returns 2006-11-14.
> 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
Here you are first converting to smalldatetime, and then convert
back to varchar without any format specification, why you get this
default format.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|