Question about returning a smalldatetime from a Function

[SQL Server]

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

[Erland Sommarskog]

SQL Server (al*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On 6 Jun 2006 01:50:03 -0700, SQL Server wrote:

(snip)

1) I tried declaring @retVal as a smalldatetime and get the error "Must
declare the variable '@retVal'.'

Hi SQL Server,

And yet, that is exactly what you should do. Never convert unless you
have to.

The error message you got is not a result of declaring @retVal as a
smalldatetime, but a result of "something" that was off in the code when
you tried that. Unfortunately, you didn't post that version of the code,
so I can't tell you what went wrong. Maybe, if you still have tat
version archived, you could post it here?

Meanwhile, try if this works:

CREATE FUNCTION dbo.test (@Group varchar(50))
RETURNS smalldatetime
AS
BEGIN
DECLARE @retVal smalldatetime
SELECT @retVal = MIN([date])
FROM dbo.t_master_schedules
WHERE event_id = 13
AND group_ = @Group
RETURN @retVal
END
--
Hugo Kornelis, SQL Server MVP

[SQL Server]

Hugo Kornelis wrote:

The error message you got is not a result of declaring @retVal as a
smalldatetime, but a result of "something" that was off in the code when
you tried that. Unfortunately, you didn't post that version of the code,
so I can't tell you what went wrong. Maybe, if you still have tat
version archived, you could post it here?
--
Hugo Kornelis, SQL Server MVP

This is okay
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

This is okay too (change Returns from varchar(50) to datetime)
CREATE FUNCTION dbo.test (@Group varchar(50))
RETURNS datetime 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

But change it to this
This is okay too (change Returns from varchar(50) to datetime)
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

Here is a link to a screen capture of the error.
http://i12.photobucket.com/albums/a2...rran/error.jpg

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.

[Erland Sommarskog]

SQL Server (al*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[SQL Server]

Erland Sommarskog wrote:

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)? .. --
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

All I want to know is how to return
08/29/2006

from
'2006-08-29 00:00:00.000'

Looking at the SQL Server Books Online help resource it appears to me
that the convert function should be able to do this. But this doesn't
work. Why not and how can I format that date the way I want in the
output. In VB I'd just use the format function. Is there something
similar in T-SQL?
print convert(datetime, '2006-08-29 00:00:00.000', 101)

[Erland Sommarskog]

SQL Server (al*********@gmail.com) writes:

All I want to know is how to return
08/29/2006

from
'2006-08-29 00:00:00.000'

Looking at the SQL Server Books Online help resource it appears to me
that the convert function should be able to do this. But this doesn't
work. Why not and how can I format that date the way I want in the
output. In VB I'd just use the format function. Is there something
similar in T-SQL?
print convert(datetime, '2006-08-29 00:00:00.000', 101)

That converts a string value to datetime. You want to convert a datetime
value to a string.

A datetime value is a internally a numeric value and does not have any
format. The format code in the above example tells SQL Server how to
interpret the string.

But as I said, while you can format date values to string in your SQL code,
you should avoid doing so. This should be done client-side, so that the
client's regional settings can be respected. I can tell you that if you
give me an app that spits out strings like 08/29/2006, you will have a bug
report back in ten seconds, because that is not a date as far as I'm
concerned.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx