locked
Error converting varchar to datetime error RRS feed

  • Question

  • ok first of all, I am not sure if this is the correct forum to as  this question. My appologies and would like to know the correct forum address.

    Now as mentioned in the subject line I am getting this errors very weirdly. The reason is, I am not passing any datetime parameters in the stored prodedure and also not doing any datetime conversion whatsoever in my stored procedure.

    My stored procedure is a pretty simple insert statement in which I have two datetime columns which are getting populated using GetDate()

    I am calling this SP from my C# code which is a part of a wcf service. From my code I AM NOT PASSING ANY DATETIME VALUES(PARAMETERS) to the stored procedure
    Another weird situation over here is, I have two WCF services one executes this stored procedure without any issues and the other fails with this error. The code bases are 100% same in both the services. The only difference between these two services are one of them is hosted on only one prod server (fails) and the other one if hosted on two prod servers(works fine). I am not sure if the server configuration has to do anything with this.

    Any inputs would be highly appreciable as I have no idea on how on earth I would get a varchar to datetime conversion error when I am not attempting any conversion at all.


    Thanks and Regards Sandeep

    • Moved by Bob Beauchemin Wednesday, July 15, 2015 9:37 PM Moved to the forum for client-side SqlClient for best results
    Wednesday, July 15, 2015 8:49 PM

All replies

  • I'd check the following:

    • Run the stored procedure manually from you developer machine
    • Run the stored procedure manually from both WCF service servers
    • Run Profiler and see what queries are run for your connection when you execute the .NET code that has the issue

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Wednesday, July 15, 2015 9:05 PM
  • Hi Daniel,

         Thanks for the reply. I performed all the steps as you suggested. Still the same issue. and more importantly both the services are showing the same results while performing these steps but when I invoke the stored procedure from my C# code, one of them works and the other one fails.


    Thanks and Regards Sandeep

    Wednesday, July 15, 2015 9:17 PM
  • Now as mentioned in the subject line I am getting this errors very weirdly. The reason is, I am not passing any datetime parameters in the stored prodedure and also not doing any datetime conversion whatsoever in my stored procedure.

    Or so you think.

    Make sure that you get the full contents of the error message, including procedure name and line number.

    Since it works on one server and fails on another, it seems that there are date values in unsafe forms in play, and the language settings are different on the two servers. But this is not a configuration issue, it is code that should be written properly.

    It certainly helps if you post the code of the stored procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 15, 2015 9:18 PM
  • Did you see any queries you didn't think about in Profiler?

    Are the WCF services connecting to the same database and instance?

    Are the WCF services running in the same timezone and language?

    Please also post the proc ddl to the forum.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)




    Wednesday, July 15, 2015 9:36 PM
  • Now as mentioned in the subject line I am getting this errors very weirdly. The reason is, I am not passing any datetime parameters in the stored prodedure and also not doing any datetime conversion whatsoever in my stored procedure.

    Or so you think.

    Make sure that you get the full contents of the error message, including procedure name and line number.

    Since it works on one server and fails on another, it seems that there are date values in unsafe forms in play, and the language settings are different on the two servers. But this is not a configuration issue, it is code that should be written properly.

    It certainly helps if you post the code of the stored procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    The full content of the error message says " The conversion of a varchar datatype to a datetime data type resulted in an out of range value. The statement has been terminated". This is the sql exception message I received in my C# code (WCF Service fault exception). It does not contain any line numbers in it. I cannot post the actual procedure but it looks something like this:

    Input Parameters: @ID (INT), @Some_Text(Varchar- 4000),@Created_By(Varchar-100)

    Query:

    INSERT INTO Table_Name

    Some_Text_Column,

     ID_Column,

    Effective_From_Column,

    Created_by_Column,

    Last_Updated_TimeStamp_Column

    Values

    (

    @Some_Text,

    @ID,

    GetDate(),

    @Created_By,

    @GetDate()

    )

    As I mentioned, it is a pretty simple (in fact the simplest) Stored Procedure we are using in your application.

    Also, can you please explain a  little more on the date values in unsafe forms?

    Thanks and Regards

    Sandeep


    Thanks and Regards Sandeep

    Thursday, July 16, 2015 1:52 AM
  • There is a typo in my reply

    the last value is GetDate() and not @GetDate()


    Thanks and Regards Sandeep

    Thursday, July 16, 2015 1:53 AM
  • Hi sandpune,

    If the type of your Effective_From_Column and Last_Updated_TimeStamp_Column are DateTime. maybe the GetDate() type is not match with DataTime. you need to convert it to relative type in SP.

    INSERT INTO Table_Name
     
    Some_Text_Column,
     
     ID_Column,
     
    Effective_From_Column,
     
    Created_by_Column,
     
    Last_Updated_TimeStamp_Column
     
    Values
     
    (
     
    @Some_Text,
     
    @ID,
     
    convert(varchar(20),GetDate(),112),
     
    @Created_By,
     
    convert(varchar(20),GetDate(),112)
     
    )

    #CAST and CONVERT (Transact-SQL)

    https://msdn.microsoft.com/en-us/library/ms187928.aspx

    Regards,
    Youjun Tang



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Youjun Tang Wednesday, July 29, 2015 4:09 AM
    • Proposed as answer by Fred Bao Friday, July 31, 2015 7:54 AM
    Wednesday, July 29, 2015 4:08 AM