none
The conversion of a varchar data type to a datetime data type RRS feed

  • Question

  • Hi all,

    I have a issue.

    I have a datatable with data as below:

    Table A:

     

    Code

    Name

    CreatedDateTime

    1

    Nam

    2010-07-20 14:02:02.343000000

     

    Table B:

     

    ID

    Name

    CreatedDateTime

    2

    Nam

    2010-07-20 14:02:02.343000000

     

    I get data from Table & insert to Table B. This is my command.

    String sql = @"INSERT INTO Test(ID, Name, CreatedDateTime) VALUES('" + row["Code"] + "' ,'" + row["Name"] + "', '" + DateTime.Parse(row["CreatedDateTime"].ToString()).ToString() + "')";
    SqlCommand MyCmd = new SqlCommand(sql , MyConnection);

    Environment 1: It can work on Regional: English(United States) & Location: United States. Collation of SQL Server: SQL_Latin1_General_CP1_CI_AS

    Environment 2: It can't work on Regional: English(Singapore) & Location: Singapore. Collation of SQL Server: Latin1_General_CI_AS

    This is error:

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    Datetime.Parse in here It may be not correct. But I don't know why It can work with Environment 1?

    I'd like to know what is root cause? Collation or Regional setting, which item decide to datetime format?

    When I change my command to:

    String sql = @"INSERT INTO Test(ID, Name, CreatedDateTime) VALUES(@ID, @Name, @CreatedDateTime)";
    SqlCommand MyCmd = new SqlCommand(sql , MyConnection);

    MyCmd.Parameters.Add("@ID",SqlType.Int).Value = row["Code"];

    MyCmd.Parameters.Add("@Name",SqlType.Varchar, 30).Value = row["Name"];

    MyCmd.Parameters.Add("@CreatedDateTime",SqlType.DateTime).Value = row["CreatedDateTime"];

    It can work with a both environment.

    Regards,

     

    Wednesday, July 21, 2010 7:56 AM

Answers

  • The parameterized solution is altogether better.  Helps prevent SQL security issues and can perform better.  Just use it.  The reason why it works is that no conversion is necessary on the database server since the parameter is declared SqlType.DateTime and thus is sent over the wire as a DATETIME not a VARCHAR(x).

    Otherwise, you need to use the forms of Parse and ToString that take a CultureInfo, and you need to figure out what those should be.  Take into account how the SQL Server is configured.

    Usually, whenever you call Parse or ToString, the result can depend on the current culture (Thread.CurrentCulture property).  Unless you set the CurrentCulture, what you get depends on the Control Panel settings.  If this is not what you want, then you need to pass a CultureInfo object.  If you want something "mostly like" USA, the CultureInfo.InvariantCulture is easy to type.  CultureInfo.InvariantCulture is great for data interchange.  Otherwise, obtain the correct CultureInfo object and pass that.  You might also be able to reconfigure the server, but that will just hide the problem--you will still need to pin down the culture used for the Parse and ToString so that your application can run correctly in different regions.

    Moreover, it is a good practice (see http://msdn.microsoft.com/en-us/library/ms182189.aspx) whenever you call a method that has an overload with a CultureInfo parameter, that you always use the overload with the CultureInfo parameter.  Use CultureInfo.CurrentCulture when that is what you mean.  By being verbose like this, it makes it clear that you are taking internationalization seriously.

     

     

    • Marked as answer by danhpc Thursday, July 22, 2010 7:41 AM
    • Unmarked as answer by danhpc Thursday, July 22, 2010 7:42 AM
    • Marked as answer by danhpc Thursday, July 22, 2010 7:43 AM
    Thursday, July 22, 2010 1:49 AM

All replies

  • The parameterized solution is altogether better.  Helps prevent SQL security issues and can perform better.  Just use it.  The reason why it works is that no conversion is necessary on the database server since the parameter is declared SqlType.DateTime and thus is sent over the wire as a DATETIME not a VARCHAR(x).

    Otherwise, you need to use the forms of Parse and ToString that take a CultureInfo, and you need to figure out what those should be.  Take into account how the SQL Server is configured.

    Usually, whenever you call Parse or ToString, the result can depend on the current culture (Thread.CurrentCulture property).  Unless you set the CurrentCulture, what you get depends on the Control Panel settings.  If this is not what you want, then you need to pass a CultureInfo object.  If you want something "mostly like" USA, the CultureInfo.InvariantCulture is easy to type.  CultureInfo.InvariantCulture is great for data interchange.  Otherwise, obtain the correct CultureInfo object and pass that.  You might also be able to reconfigure the server, but that will just hide the problem--you will still need to pin down the culture used for the Parse and ToString so that your application can run correctly in different regions.

    Moreover, it is a good practice (see http://msdn.microsoft.com/en-us/library/ms182189.aspx) whenever you call a method that has an overload with a CultureInfo parameter, that you always use the overload with the CultureInfo parameter.  Use CultureInfo.CurrentCulture when that is what you mean.  By being verbose like this, it makes it clear that you are taking internationalization seriously.

     

     

    • Marked as answer by danhpc Thursday, July 22, 2010 7:41 AM
    • Unmarked as answer by danhpc Thursday, July 22, 2010 7:42 AM
    • Marked as answer by danhpc Thursday, July 22, 2010 7:43 AM
    Thursday, July 22, 2010 1:49 AM
  • Thank you so much.

    It clear up my mind.

    Regards,

    DanhPC

    Thursday, July 22, 2010 7:43 AM