locked
Need help regarding date time conversion error? RRS feed

  • Question

  • User743508062 posted

    Hi I am getting the following error:

    Server Error in '/' Application.
    Conversion failed when converting date and/or time from character string.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
    
    Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
    
    Source Error:
    
    
    Line 29: 
    Line 30:         var edsql2 = "UPDATE tblLIQuantity SET quantity=@0, inventdtstamp=@1 WHERE detmaterial=@2";
    Line 31:         db.Execute(edsql2, edquantity, edDTS, detmaterial); 
    Line 32: 
    Line 33:         Response.Redirect("~/rendStorage2/" + lid);

    This seems to be related to the way dates are being used in the column called:  inventdtstamp.

    If I iput dates in this style: mm/dd/yy then it is OK.

    However we use the dd/mm/yy style and this throws the error above.

    I have chnaged sql server express's language settings to British English but no luck.

    How can I overcome this issue?

    Thanks again.

    Thursday, July 25, 2013 6:11 AM

Answers

  • User743508062 posted

    I think I solved it, and just in case someone else needs the same solutionm this is what I did:

    Go into SQl studio management (SSMS)

    Locate the object explorer (shoud be on the left) this will list the SQL server and the various databases.

    Locate the folder called security, open this folder and locate the user account, right click this account, select properties and on the first page, go to the bottom and select the language for that account (in my case British english).

    Now my dates are being saved in dd/mm/yy format.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 25, 2013 7:13 AM

All replies

  • User743508062 posted

    I think I solved it, and just in case someone else needs the same solutionm this is what I did:

    Go into SQl studio management (SSMS)

    Locate the object explorer (shoud be on the left) this will list the SQL server and the various databases.

    Locate the folder called security, open this folder and locate the user account, right click this account, select properties and on the first page, go to the bottom and select the language for that account (in my case British english).

    Now my dates are being saved in dd/mm/yy format.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 25, 2013 7:13 AM
  • User-821857111 posted

    Best advice is not to relay on regional settings (which can change from server to server) but to store dates in iso format:

    yyyy-mm-dd hh:mm:ss

    Invariably, the tools that allow you to see dates stored in a database rely on regional settings but have no bearing on how a value is stored.

    Thursday, July 25, 2013 7:55 AM
  • User743508062 posted

    Mike, in response to your post above, I went into the table and had a look and yes you are right the dates/times are stored as:

    yyyy-mm-dd hh:mm:ss,

    However, from the users point of view they can insert a new data using the British style without the system throwing an error.

    Thanks for the information and help.

    Thursday, July 25, 2013 8:01 AM
  • User-821857111 posted

    However, from the users point of view they can insert a new data using the British style without the system throwing an error.

    You should take any "dates" provided by users and reformat them for insertion. You can largely control how they provide a date through UI design. I tend to use the jQuery UI datepicker for date selection and specify the format I want. Users just pick a date. They don't care about the format.

    Thursday, July 25, 2013 9:49 AM
  • User743508062 posted

    Mike do you have a tutorial or sample I can emulate? Thanks.

    Thursday, July 25, 2013 10:16 AM