468,103 Members | 1,301 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,103 developers. It's quick & easy.

'Syntax error converting datetime from character string' with sp_executesql

CREATE PROCEDURE dbo.Synchronization_GetNewRecords
(
@item varchar(50),
@last datetime
)

AS

SET NOCOUNT ON

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @last

EXEC sp_executesql @sql, N'@Type varchar(50), @Last datetime', @item, @last
This is my SP. Very simple. But it is throwing the error in the subject line.

Any help would be greatly appreciated.
Jul 20 '05 #1
1 38165
ju*********@iomer.com (Justin Wong) wrote in message news:<76**************************@posting.google. com>...
CREATE PROCEDURE dbo.Synchronization_GetNewRecords
(
@item varchar(50),
@last datetime
)

AS

SET NOCOUNT ON

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @last

EXEC sp_executesql @sql, N'@Type varchar(50), @Last datetime', @item, @last
This is my SP. Very simple. But it is throwing the error in the subject line.

Any help would be greatly appreciated.


There are a couple of issues here - you seem to be mixing the syntax
for EXEC() and sp_executesql; the error is because datetime has a
higher precedence than nvarchar, so the string is implicitly converted
to a datetime, which won't work. You need to explicitly cast or
convert the datetime.

In fact, in this case you can't use sp_executesql anyway, because it
won't accept a variable in place of the table name. You could use
EXEC() (see code below), but you probably shouldn't:

http://www.sommarskog.se/dynamic_sql.html#Dyn_table

Finally, if you do use this approach, you will need to use a safe
format for the datetime parameter, or you may get the same error
again, eg.:

'20040220' -- works everywhere
'20/02/2004' -- fails with US English

Simon
CREATE PROCEDURE dbo.Synchronization_GetNewRecords
@item varchar(50),
@last datetime

AS

SET NOCOUNT ON

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated > ''' +
cast(@last as nvarchar(50)) + ''''

EXEC (@sql)
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by DBA | last post: by
11 posts views Thread by jguilford | last post: by
4 posts views Thread by Carl | last post: by
1 post views Thread by amitbadgi | last post: by
14 posts views Thread by eric.goforth | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.