Post reply

Error: Syntax error converting datetime from character string

  • 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

  • August 15, 2005 at 9:40 am

    #582217

    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.

  • August 15, 2005 at 9:42 am

    #582219

    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 🙂

  • August 15, 2005 at 9:53 am

    #582223

    Thank you both for your help.

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

  • August 16, 2005 at 2:02 am

    #582369

    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

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

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