Syntax error converting datetime from character st

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Syntax error converting datetime from character st

Author

Topic

jmaxsin
Starting Member

4 Posts

Posted - 2007-08-14 : 10:02:15

Here's the stored procedure:

CREATE PROCEDURE [dbo].[GetPolicyRequests]
(
@planno as varchar(50),
@sort as varchar(10)
)
AS

SELECT ID, plan_nbr, IP_address, first_name, middle_initial, last_name, street_address, city, state_abbrv, postal_code, convert(varchar, date_stamp, 100)
FROM policy_requests
WHERE (plan_nbr = @planno)
ORDER BY

CASE @sort
WHEN 'city' then city + ',' + state_abbrv + ',' + postal_code + ',' + last_name + ',' + first_name + ',' + middle_initial + ',' + date_stamp
WHEN 'address' then street_address + ',' + city + ',' + state_abbrv + ',' + postal_code + ',' + last_name + ',' + first_name + ',' + middle_initial
WHEN 'zip' then postal_code + ',' + city + ',' + state_abbrv + ',' + last_name + ',' + first_name + ',' + middle_initial
WHEN 'firstname' then first_name + ',' + middle_initial + ',' + last_name + ',' + city + ',' + state_abbrv + ',' + postal_code + ',' + street_address
WHEN 'state' THEN state_abbrv + ',' + postal_code + ',' + city + ',' + last_name + ',' + first_name + ',' + middle_initial
ELSE last_name + ',' + first_name + ',' + middle_initial + ',' + city + ',' + state_abbrv + ',' + postal_code + ',' + street_address
END
GO

My problem is that when I add the date_stamp field to the order by clause of any case, I receive this error when executing it wit query analyzer: Syntax error converting datetime from character string.

If I remove the date_stamp field from the order by clause it runs fine.

I have tried converting the date_stamp field to varchar, datetime; I've tried cast.

If I write a regular order by clause (one with out a case statement) and include the date_stamp field it runs fine.

Any ideas as to what I might be doing wrong?

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 11:55:57

DUPLICATE POST

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 12:00:55

USE NORTHWIND

GO

declare @orderid int
set @orderid = 10248
select case when orderid = @orderid
then
convert(varchar(10),orderID)+', '+convert(varchar(12),orderdate)
+', '+convert(varchar(12), requireddate)
else
convert(varchar(10),orderID)+', '+convert(varchar(12),orderdate)
+', '+convert(varchar(12), requireddate) END
from orders

Ashley Rhodes

jmaxsin
Starting Member

4 Posts

Posted - 2007-08-14 : 12:01:19

If this is a duplicate post could you please indicate how I can review the other post. I've seen other similar error messages but nothing that comes close to the complexity of my stored procedure. Or a solution that works for me.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 12:02:03

The above query works fine. What kind of data type is postal code

CASE @sort
WHEN 'city' then city + ',' + state_abbrv + ',' + postal_code + ',' + last_name + ',' + first_name + ',' + middle_initial + ',' + convert(varchar(12),date_stamp)

Ashley Rhodes

jmaxsin
Starting Member

4 Posts

Posted - 2007-08-14 : 12:03:55

Nevermind. The page was not showing the example. This worked! Thanks so much!!!

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 12:04:25

you need to convert when you concatenate every column with INT datatype and datetime datatype
if postal code is int then

CASE @sort
WHEN 'city' then city + ',' + state_abbrv + ',' + convert(varchar(5),postal_code) + ',' + last_name + ',' + first_name + ',' + middle_initial + ',' + convert(varchar(12),date_stamp)

Ashley Rhodes

jmaxsin
Starting Member

4 Posts

Posted - 2007-08-14 : 13:20:19

Awesome! Thanks a bunch!

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 13:42:12

you are welcome

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources