Posted by Erland Sommarskog on 06/07/06 21:59

SQL Server (alderran666@gmail.com) writes:
> CREATE FUNCTION dbo.test (@Group varchar(50))
> RETURNS datetime AS
> BEGIN
> Declare @retVal datetime
> (SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
> (event_id = 13) AND (group_ =@Group))
> return convert(smalldatetime, @retVal, 1)
> END
>...
> the column [date] in the table t_master_schedules is a datetime.
>
> I actually do want @retVal to be a varchar because the end result
> should be a string that shows the first date for a particular group and
> the last date in a particular group. So I would be running a select
> with a Max([date]) and returning a string
>
> 11/14/2006 and 02/03/2007
>
> The problem is that I am not able to get the date formated into the
> mm/dd/yyyy format that I want.

If you want a string back, why do you then insist on converting to
smalldatetime? Should you not convert to char(10) and return char(10)?

Anyway, I would suggest that you scrap the function entirely. I don't
know where you use this function, but data access from scalar functions
should be avoided, as it can affect performance considerably if
you stick into a query. This is because the query more or less get
converted to a cursor behind the scenes. So it is much better to
integrate the logic in the main query.

As for the date formatting, you should avoid formatting dates in
SQL Server, but format them client side, so the the client's
regional settings are respected.
--
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

[Back to original message]