473,558 Members | 2,874 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Strange datetime conversion error in stored procedure.

Hi Everyone,

I've been battling this for two days with no luck. I'm using SQL
Server 2000.

Here's the mystery: I've got a stored procedure that takes a single
varchar parameter to determine how the result set is sorted. Here it
is:

CREATE PROCEDURE spDemo @SortField varchar(30)

AS
SELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDesc
FROM ActivityLog
ORDER BY CASE @SortField
WHEN 'dtmTimeStamp' THEN dtmTimeStamp
WHEN 'strEmpName' THEN strEmpName
WHEN 'strOld' THEN strOld
WHEN 'strNew' THEN strNew
WHEN 'strActionDesc' THEN strActionDesc
END
GO

When I execute the stored procedure in the Query Analyzer, it works
perfectly ONLY IF the @SortField parameter is 'dtmTimeStamp' or
'strNew'. When passing in any of the other three possible values for
@SortField, I get the following error:

Server: Msg 241, Level 16, State 1, Procedure spDemo, Line 4
Syntax error converting datetime from character string.

Now instead of executing the stored procedure, if I copy and paste the
SELECT statement directly into the Query Analyzer (after removing the
CASE statement and manually trying each different value of @SortField),
it works fine for all five possible values of SortField.

Even though the error points to Line 4 of the stored procedure, it
seems to me that the CASE statement is causing problems for some, but
not all, values of the @SortField parameter.

Any ideas?

Thanks,
Jimmy

Jul 23 '05 #1
4 9550
RT
Check the data type for each of the column. when using case statement
in the order by clause it should be the same. If i am not wrong
"strNew" column is also a datetime field. That's why you are not
getting any error for strNew. Since the first case statement is trying
to sort "dtmTimeSta mp" which is datetime datatype, when you try to pass
the parameter as "strEmpName " it is trying to convert the strEmpName to
string. I would suggest adding a convert function to all datatypes
which are not character datat type and convert it to a string. for date
you should use covnert(varchar (50),dtmTimeSta mp, 21) if you want to
sort upto the mlli second. use the same way for integer. This is a good
option if you are sorting only smaller set of rows. If you are sorting
a larger set trying having separate sql's for each order. i hope this
helps.

Thanks
Ramesh

Jul 23 '05 #2
On 6 Jan 2005 08:51:35 -0800, sh****@gmail.co m wrote:
Hi Everyone,

I've been battling this for two days with no luck. I'm using SQL
Server 2000.

Here's the mystery: I've got a stored procedure that takes a single
varchar parameter to determine how the result set is sorted. Here it
is:

CREATE PROCEDURE spDemo @SortField varchar(30)

AS
SELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDesc
FROM ActivityLog
ORDER BY CASE @SortField
WHEN 'dtmTimeStamp' THEN dtmTimeStamp
WHEN 'strEmpName' THEN strEmpName
WHEN 'strOld' THEN strOld
WHEN 'strNew' THEN strNew
WHEN 'strActionDesc' THEN strActionDesc
END
GO

(snip)

Hi Jimmy,

The explanation Ramesh gives you is correct. However, instead of manually
converting all data to the same datatype (which is often clumsy and can
even make proper sorting impossible), you should use several CASE
statement:

ORDER BY CASE WHEN @SortField = 'TimeStamp' THEN TimeStamp END,
CASE WHEN @SortField = 'EmpName' THEN EmpName END,
....
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3
Hugo, I just fell in love with you. It worked.

Thanks,
Jimmy

Jul 23 '05 #4
On 6 Jan 2005 13:36:36 -0800, sh****@gmail.co m wrote:
Hugo, I just fell in love with you. It worked.


LOL!

I'm sorry Jimmy - I'm already married <g>

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #5

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

Similar topics

24
4718
by: LineVoltageHalogen | last post by:
Greetings All, I was hoping that someone out there has run into this issue before and can shed some light on it for me. I have a stored procedure that essentially does a mini ETL from a source OLTP DB to a the target Operational Data Store. This is still in development so both DB's reside on the same machine for convenience. The stored proc...
3
1685
by: Jim in Arizona | last post by:
I'm going insane! I don't know if it's just that the .net 2.0 framework is buggy or if it really is my code. This is pretty hard to explain since I can't even begin to nail down why this is happening. I have two text boxes. One is for the ID number, which when postback occurs, inserts into the related table as the foreign key. The other...
11
7201
by: Cor Ligthert | last post by:
Hello everybody, Jay and Herfried are telling me every time when I use CDate that using the datetime.parseexact is always the best way to do String to datetime conversions. They don't tell why only that I have to listen to them because they know it better. They told also that in a business situation it is better to use...
4
1359
by: | last post by:
Im getting that error (It is strange. I Run my programme step by step pressing f11. im looping SqlCommand in a while loop. The First step runs but when a enter 2nd step in my loop it returns error ? ) any idea ? System.Data.SqlClient.SqlException: Procedure or function sp_adduser has too many arguments specified My Stored Procedure
4
7222
by: =?Utf-8?B?QmFidU1hbg==?= | last post by:
Hi, I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD operations. The DataSource has three columns one of which - "Modified" of type DateTime - is hidden since it should not be edited by a user. The system handles the update for this column. So, I have hidden...
1
31331
by: Brad Pears | last post by:
I am using vb.net 2005 and SQL server 2000. In my table I have a date field of type "smalldatetime". In my vb application, the user may or may not enter a date value into the appropriate text box. I then want to pass the value of this text box as a datetime variable to my stored procedure which inserts or updates the row. As I mentioned...
3
1520
by: Vish4u | last post by:
Hello Everyone, I have a encountered a strange issue with the execution of my stored procedure on clients machine. My stored procedure contains a cursor in which there is a select statement written in concatinated string format (provided below). when i execute the procedure on my clients database some concatinated lines in the select...
5
4725
by: Sreenivas | last post by:
Hi every one, I need to compare to datetime values , done in stored procedure.The input datetime parameter is in dd/mm/yyyy hh:mm:ss AM/PM format ,the datetime values to be compared are also stored with the same format. does datetime works fine for AM/PM format also? because some test conditions are failing , which i thought correct..Help...
2
2621
by: sirdavethebrave | last post by:
Hi guys - I have written a form, and a stored procedure to update the said form. It really is as simple as that. A user can go into the form, update some fields and hit the update button to update the information which is stored in a SQL database. In testing we noticed that the form was updating correctly but the update mechanism was also...
0
7629
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7549
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7914
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6183
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3602
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2045
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1164
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.