none
Conversion failed when converting datetime from character string. RRS feed

  • Question

  • Hi,
    I am getting this error when I am sending in a date into a stored Procedure. Any ideas why?

    The date source comes from an asp.net calander control:

     
    DateTime date = new DateTime();
    date = calDateSelected.SelectedDate.Date;
    

    I then send in the arguments into my stored Procedure:

    db.search_FindFiles(X,X,X,X,X,X,X,X,date,X,X);
    

    The Procedure then recieves the data:

    PROCEDURE [dbo].[search_FindFiles] 
    	(@Search1 varchar(50), 
    	@Search2 varchar(50), 
    	@Search3 varchar(50), 
    	@Search4 varchar(50), 
    	@Search5 varchar(50), 
    	@TypeID varchar(50),
    	@DateType varchar(50),
    	@DateOperator varchar(50),
    	@Date datetime,
    	@Table int,
    	@Content bit)
    

    Here is an example as to when I use @Date :

    'WHERE nf.'+ @DateType +' '+ @DateOperator +' '+ @Date +''
    

    DateType is specifying a Field in a table. In my case its between check against the Creation Date and the Modified Date. Operator would either be >, =, <.

    Hope this is enough information. Let me know if you need anything else. Thanks for any help that you offer.

    -SS
    Thursday, June 25, 2009 3:59 PM

Answers



  • Here is an example as to when I use @Date :

    'WHERE nf.'+ @DateType +' '+ @DateOperator +' '+ @Date +''
    
    


    If you're concatenating a string inside the stored proc (and running using exec/sp_execute (?)) you need to put quotes around the date. You'll also need to quote-escape the varchar params and/or pass them as parameters to sp_execute to avoid sql injection attacks...
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools, www.huagati.com/L2SProfiler
    • Marked as answer by Splitterside Thursday, June 25, 2009 7:38 PM
    Thursday, June 25, 2009 4:16 PM
    Answerer
  • I just figured out that I didn't have to compare the date to a DateTime variable and that just passing in a string in the correct format works just fine.
    • Marked as answer by Splitterside Thursday, June 25, 2009 7:39 PM
    Thursday, June 25, 2009 7:39 PM

All replies



  • Here is an example as to when I use @Date :

    'WHERE nf.'+ @DateType +' '+ @DateOperator +' '+ @Date +''
    
    


    If you're concatenating a string inside the stored proc (and running using exec/sp_execute (?)) you need to put quotes around the date. You'll also need to quote-escape the varchar params and/or pass them as parameters to sp_execute to avoid sql injection attacks...
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools, www.huagati.com/L2SProfiler
    • Marked as answer by Splitterside Thursday, June 25, 2009 7:38 PM
    Thursday, June 25, 2009 4:16 PM
    Answerer
  • You'll also need to quote-escape the varchar params and/or pass them as parameters to sp_execute to avoid sql injection attacks...
    Can you clarify what you mean here and or give an example?
    Thursday, June 25, 2009 5:06 PM
  • I have also learned that it is not just that part of the code. I have tried to execute the proc with the proc just printing the SQL string to exce and I am still getting a similar error:

    Error converting data type varchar to datetime.
    Thursday, June 25, 2009 5:45 PM
  • I just figured out that I didn't have to compare the date to a DateTime variable and that just passing in a string in the correct format works just fine.
    • Marked as answer by Splitterside Thursday, June 25, 2009 7:39 PM
    Thursday, June 25, 2009 7:39 PM