469,898 Members | 1,408 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Problem with DateTime and strings in stored procedures

I'm keeping in the database a log of all the sessions for my
application. I'm trying to write a stored procedure that returns all
the sessions that; the login contains a certain string, loggedin after
a certain datetime and loggedout before another datetime. Any
combination of these parameters can be used and, if none, returns all
the log.

Below is the code I came up with but I'm having a "Syntax error
converting datetime from character string" exception. When not using
DateTime parameters everything works fine. Can you tell me how can I
avoid this exception? Thanks in advance...

ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
(
@Pattern Varchar(255),
@From DateTime,
@To DateTime
)
AS
DECLARE @Query VARCHAR(500)
SET @Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
SessionHistoryItems.SessionId = Sessions.SessionId'

DECLARE @conditions nvarchar(257)
SET @conditions = '';

IF LEN(@Pattern) > 0 BEGIN
SET @conditions = @conditions + ' Sessions.Login LIKE ''%' + @Pattern
+ '%'''
END

IF @From IS NOT NULL BEGIN
IF LEN(@conditions) > 0 BEGIN
SET @conditions = @conditions + ' AND '
END
SET @conditions = @conditions + ' SessionHistoryItems.LoggedOutAt >=
' + @From
END

IF @To IS NOT NULL BEGIN
IF LEN(@conditions) > 0 BEGIN
SET @conditions = @conditions + ' AND '
END
SET @conditions = @conditions + ' SessionHistoryItems.LoggedInAt <= '
+ @To
END

IF LEN(@conditions) > 0 BEGIN
EXEC(@Query + ' AND ' + @conditions)
END
ELSE BEGIN
EXEC(@Query)
END
RETURN

Jul 23 '05 #1
1 1816

<an***@iilab.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
I'm keeping in the database a log of all the sessions for my
application. I'm trying to write a stored procedure that returns all
the sessions that; the login contains a certain string, loggedin after
a certain datetime and loggedout before another datetime. Any
combination of these parameters can be used and, if none, returns all
the log.

Below is the code I came up with but I'm having a "Syntax error
converting datetime from character string" exception. When not using
DateTime parameters everything works fine. Can you tell me how can I
avoid this exception? Thanks in advance...

ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
(
@Pattern Varchar(255),
@From DateTime,
@To DateTime
)
AS
DECLARE @Query VARCHAR(500)
SET @Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
SessionHistoryItems.SessionId = Sessions.SessionId'

DECLARE @conditions nvarchar(257)
SET @conditions = '';

IF LEN(@Pattern) > 0 BEGIN
SET @conditions = @conditions + ' Sessions.Login LIKE ''%' + @Pattern
+ '%'''
END

IF @From IS NOT NULL BEGIN
IF LEN(@conditions) > 0 BEGIN
SET @conditions = @conditions + ' AND '
END
SET @conditions = @conditions + ' SessionHistoryItems.LoggedOutAt >=
' + @From
END

IF @To IS NOT NULL BEGIN
IF LEN(@conditions) > 0 BEGIN
SET @conditions = @conditions + ' AND '
END
SET @conditions = @conditions + ' SessionHistoryItems.LoggedInAt <= '
+ @To
END

IF LEN(@conditions) > 0 BEGIN
EXEC(@Query + ' AND ' + @conditions)
END
ELSE BEGIN
EXEC(@Query)
END
RETURN


It looks like you need to CAST or CONVERT the datetime to a string (and add
quotes) in order to build up the @conditions string:

declare @dt datetime
set @dt = getdate()

select 'x' + @dt -- fails
select 'x''' + cast(@dt as varchar(20)) + '''' -- succeeds

But in this case, using sp_executesql would probably be a better approach
anyway:

exec sp_executesql
N'select col1, col2 from dbo.MyTable where datecol >= @From and datecol <=
@To',
N'@From datetime, @To datetime',
@From, @To

See sp_executesql in Books Online, and also these articles for more
information/ideas:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html

Simon
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Demetris | last post: by
2 posts views Thread by Steven Blair | last post: by
8 posts views Thread by craigkenisston | last post: by
30 posts views Thread by dbuchanan | last post: by
4 posts views Thread by =?Utf-8?B?QmFidU1hbg==?= | last post: by
6 posts views Thread by Mark | last post: by
5 posts views Thread by Sreenivas | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.