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?
TIASQL 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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|||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/a...erran/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.|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/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)|||SQL Server (alderran666@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
No comments:
Post a Comment