locked
Error converting nvarchar to datatime RRS feed

  • Question

  • Hi,

    We have a web service which invokes a call to a stored procedure. There are 2 input parameters for the stored proc. datatype of them are INT & User Defined Data Table type.

    User Defined Data table type contains couple of datetime column (their datatype is Datetime). Service layer of the application creates a datatable with default datatype i.e. string for all the columns. Then this datatable is assigned to User Defined Data Table Type parameter using SQLParameter function and then the stored procedure is called.

    Now the issue is if we pass a date '2/9/2018 10:00:00 AM' in the datatable, everything works fine i.e. I can trace the call of the stored procedure in the profiler & the stored procedures returns the desired resultset

    But if we pass a date '26/2/2018 7:00:00 AM' in the datatable, the code breaks and we can see below error in event log file.

    ERROR : Msg 242, Level 16, State 3, Line 2
    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

    Also I am not able to trace this stored procedure in the profiler.

    However, we tried to change the db connection for the same service to another database and tried running it using '26/2/2018 7:00:00 AM' date, the code is running fine there i.e. I can trace the call of the stored procedure in the profiler & the stored procedures returns the desired resultset.

    I copied the call of the stored procedure and tried to run the same code in SSMS on the db where we were getting error from service call, and it is running all good there. Date format is '26/2/2018 7:00:00 AM'.

    Collation property is set to - SQL_Latin1_General_CP1_CI_AS for both the DB

    Dateformat property is set to - mdy for both the DB

    Default Language is British English (where we get error) and  English (where we do not get error)

    DateTime format setting of the server where the service and website is hosted is 'mm/dd/yyyy' for both the server

    Can anyone please suggest a possible solution for this issue.



    Sandeep Prajapati

    Monday, February 26, 2018 7:38 AM

Answers

  • This is a typical issue that happens due to server interpreting the date literal values based on its regional settings

    I've written on it here

    https://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

    The solution which is always guaranteed to work is to pass date values in unambiguos ISO format i.e yyyyMMdd while passing through literals

    So you need to tweak the application code to convert date values to this format before you pass it down to sql. Then it will work fine regardless your your servers dateformat and language settings


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 26, 2018 8:00 AM

All replies

  • This is a typical issue that happens due to server interpreting the date literal values based on its regional settings

    I've written on it here

    https://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

    The solution which is always guaranteed to work is to pass date values in unambiguos ISO format i.e yyyyMMdd while passing through literals

    So you need to tweak the application code to convert date values to this format before you pass it down to sql. Then it will work fine regardless your your servers dateformat and language settings


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 26, 2018 8:00 AM
  • Thank you Visakh for providing your insights on the issue

    Sandeep Prajapati

    Monday, February 26, 2018 9:25 AM