Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    47

    Question Unanswered: Syntax error converting datetime from character string

    Hi I get the error "Syntax error converting datetime from character string "for
    the following sql statement in Stored proc.

    SET @dynamicSQL =N'SELECT CUSTID,SHCodeLink FROM AccountMaster where CONVERT(DATETIME,OPDATE,105) < = '''+CONVERT(DATETIME,@ACCYEAR,105) +''' And GLCode = '+@DIVGLCODE

    EXEC(@dynamicsql)

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give us the values of OPDATE and @ACCYEAR ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2009
    Posts
    47

    Question

    I m using sql2000...
    Value in Opdate comes from table it is 03-12-2008..Whereas @AccYear I m passing from my code..While passing I m passing it as a date and its value is 10-12-2008.But when I printed the value in @Accyear in my SP it displays time also..Maybe bcoz of tht I m getting error..But I m unable to remove it..

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Get rid of the dynamic SQL, there is your problem.
    Code:
    < = '''+CONVERT(DATETIME,@ACCYEAR,105) +''' A
    You are trying to embed a DATETIME in a string. I also wonder why you want to convert the datetime @AccYear to a datetime.

    Rewrite it like this:
    Code:
    SELECT CUSTID, SHCodeLink 
    FROM AccountMaster 
    where CONVERT(DATETIME, OPDATE, 105) <= @ACCYEAR And 
          GLCode = @DIVGLCODE
    Try to write "<=" correct (not "< ="), some environments don't like that.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2009
    Posts
    47
    Hi Thnks for the Post..It worked actually I was using DynamicSql for some other purpose...See if you could help me with the same solution using dynamic sql otherwise the solution is perfect...Thnks

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    One has to convert the dates to the same type to be able to compare them, I have converted them to YYYY-MM-DD format. You could also convert them both to a date and compare those dates.
    Code:
    DECLARE @dynamicSQL NVARCHAR(500)
    DECLARE @OPDATE CHAR(10)
    DECLARE @ACCYEAR DATETIME
    
    SET @OPDATE = '03-12-2008'
    SET @ACCYEAR = CONVERT(DATETIME, '10-12-2008', 105)
    
    SET @dynamicSQL =N'SELECT CASE WHEN CONVERT(VARCHAR(10), CONVERT(DATETIME, ''' + @OPDATE + ''', 105), 120) <= ''' + CONVERT(VARCHAR(10), @ACCYEAR, 120) + ''' THEN 1 ELSE 0 END'
    print @dynamicSQL
    EXEC(@dynamicsql)
    I added the print statement for debugging purposes.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •