Error: Syntax error converting datetime from character string

Question

Error: Syntax error converting datetime from character string

Brendan Collins

SSC Eights!

Points: 852
More actions
August 15, 2005 at 9:27 am

#89501

Hi
I have the below stored procedure in my database and am getting the error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
While debugging my web page it shows the stored procedure getting called with the following data:
@ProjectID = 1
@DateFrom = 02/10/2005
@DateTo = 08/10/2005
@Title = NULL
Anyone see what I am doing wrong in my SQL code? I have tried modifying that data type for the dates to varchar(10), but with no improvements.
All help appreciated.
Thanks
Brendan
--============================================
-- Get all matching documents for user selected criteria
--============================================
CREATE PROCEDURE spGetDocuments
@ProjectID int,
@DateFrom datetime,
@DateTo datetime,
@Title varchar(150)
AS
Declare @SQLString nvarchar(500)
Set @SQLString = 'SELECT AttId, AttFileDesc, CTimeStamp, AttFileName, AttAbstract FROM FileAttachments '
Set @SQLString = @SQLString + ' WHERE ProjectId = ' + CAST(@ProjectID AS varchar(10))
if Len(@Title) > 0
Begin
Set @SQLString = @SQLString + ' AND AttFileDesc LIKE ''%' + @Title + '%'''
End
if Len(@DateFrom) > 0 AND Len(@DateTo) > 0
Begin
Set @SQLString = @SQLString + ' AND CTimeStamp BETWEEN '' + @DateFrom + '' AND '' + @DateTo + '''
End
Execute sp_executesql @SQLString
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 1

Substitute this line of code after the "IF LEN (@Datefrom)...BEGIN":

Set @SQLString = @SQLString + ' AND crdate BETWEEN ' + CHAR (39) + CONVERT (VARCHAR, @DateFrom, 101) + CHAR (39)
+ ' AND ' + CHAR (39) + CONVERT (VARCHAR, @DateTo, 101) + CHAR (39)

The CHAR (39) values are just explicit single-quotes -- I get confused trying to figure out how many to concatenate when playing them straight. The essential difference is that you were trying to concatenate DATETIME variables to character strings. You need to convert them first.

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 2

Have you tried single quotes around the date?

Also, 2 cents: the structure of the stored proc uses a string to compose dynamic SQL, which will work but might not be the best method. I would rewrite to use direct SQL commands instead of composing a string and then executing it. It'll be faster, and help to secure against injection attacks.

Oops Lee and I were typing at the same time 🙂

Brendan Collins SSC Eights! Points: 852 More actions · Answer 3

Thank you both for your help.

As always with this forum, the help has been spot on.

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 4

Hi,

anytime you handle date/time values you should use SET DATEFORMAT, in your case SET DATEFORMAT dmy. I've had a lot of trouble with datetime, because the format does change sometimes if you install fixes or software either on a server, or on a client...

SET DATEFORMAT keeps you save 😉

regards karl

Best regards
karl