473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Question about returning a smalldatetime from a Function

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

Jun 6 '06 #1
6 7613
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
Jun 6 '06 #2
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
Jun 6 '06 #3

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.

Jun 7 '06 #4
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
Jun 7 '06 #5
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)

Jun 7 '06 #6
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
Jun 8 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Asif | last post by:
Hi there, I have been trying to understand the behaviour of char (*pfn)(null) for a couple of days. can some body help me understand the behaviour of char (*pfn)(null) in Visual C++ environment?...
8
by: Alex | last post by:
My table is laid out as such: ID (int) What (varchar 20) TimeStamp (smalldatetime) ------- ------------- --------------- 73 Start ...
1
by: garydevstore | last post by:
Hi, I have 2 tables, Mail_subject and Mail_Usage. Mail_Subject contains the subject, body and some other bits of info. CREATE TABLE ( NOT NULL , (50) COLLATE...
2
by: Marc Pelletier | last post by:
Hello all, I have a library of datetime routines that make things simpler for me. Before I discovered DateTime.MinValue I had a function that looked like public static DateTime DayZero { get {...
2
by: .Net Sports | last post by:
I'm using these to assign a variable to a smalldatetime object in sql server: dim todnews = DateTime.Today.ToString ( "d" ) 'connection string to server is on this line Dim strSQL2 as string...
5
by: Dimitri Furman | last post by:
This looks like a bug - hopefully somebody can explain what is actually happening. Using SQL Server 2000 SP4. Here's a repro script with comments: /* repro table */ CREATE TABLE dbo.T ( ID...
33
by: jobo | last post by:
If I have the function: int f(int (*h)(int)) { return (*h)(13); } What exactly does (int (*h)(int)) do? So it's taking a pointer but what's with the two ints? Thanks.
2
by: justin | last post by:
Hello all: I have a FormView that has it's DataSourceID set to a SqlDataSource that uses stored procedures for it's select, update, insert, and delete. I want the FormView to have an "autosave"...
4
by: vincibleman | last post by:
Howdy all, Working my way into SQL from Access. Think I might have the hang of the basics, but would really appreciate a sanity check. The stored procedure listed below works, but I can't help...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.