none
Problem SQL Server 2008 -> Insert a datetime -> out-of-range value RRS feed

  • Question

  • Hi everybody !

    I have installed a software on a server that is running SQL Server 2008 an when I am trying to insert a record, I get the following message :

     

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

     

    The same software works fine on SQL Server 2000.

     

    The only difference is that on the SQL Server 2000 one, the language of the OS is french and on the SQL Server 2008 one, it's English.

    The C# code behind uses :

     

    DateTime.Now.ToString()

     

    and the result is : '16.04.2010 13:18:18'

     

    What can I do ? Do have I to change my C# code to be compatible with all the regional settings ? Can I set the regional settings on the database to make it work ?

    I see that the following code works fine :

     

     

    DateTime.Now.ToString("s")

     

     

    and the result is : '2010-04-16T13:30:27'

     

     

    I don't know what can append if I change all the datetime stuff in my app with the "s" parameter.

    Also, I am worried about other problems that I can have with regional settings.

    Thanks 4 helping me !

     

    Best regards.

     

    MatteoDev

    Friday, April 16, 2010 11:29 AM

Answers

  • Ideally, you would provide the data to SQL Server using the .NET DateTime data type, not a String data type.  This will avoid the VARCHAR to DATETIME conversion that is at the heart of the trouble.

    The reason why it works in English is because English dates are MM/dd/yyyy while French dates are dd/MM/yyyy.

    The "s" format produces an ISO 8601 standardized date and time.  This format is precisely specified -- it is independent of the system's culture, both .NET and SQL Server know to not do anything culture-specific when they encounter this format.

    My recommendation is to try to avoid the conversion altogether.  This might be a symptom of your application concatenating values into SQL statements.  If this is the case, this is generally bad from a security perspective.  Research "parameterized SQL".

     

     

     

    Friday, April 16, 2010 11:02 PM

All replies

  • Ideally, you would provide the data to SQL Server using the .NET DateTime data type, not a String data type.  This will avoid the VARCHAR to DATETIME conversion that is at the heart of the trouble.

    The reason why it works in English is because English dates are MM/dd/yyyy while French dates are dd/MM/yyyy.

    The "s" format produces an ISO 8601 standardized date and time.  This format is precisely specified -- it is independent of the system's culture, both .NET and SQL Server know to not do anything culture-specific when they encounter this format.

    My recommendation is to try to avoid the conversion altogether.  This might be a symptom of your application concatenating values into SQL statements.  If this is the case, this is generally bad from a security perspective.  Research "parameterized SQL".

     

     

     

    Friday, April 16, 2010 11:02 PM
  • Thanks a lot for these information. Actually, I have no way to change the way I do, but using the ISO format seems a good solution. Best regards. MatteoDev
    Monday, April 19, 2010 8:11 AM
  • The other thing you could consider is setting the Thread.CurrentThread.CurrentCulture to a CultureInfo.GetCultureInfo that matches what your SQL Server is using (e.g., "en-US"/"fr-FR").  In this way the default MM/dd/yyyy vs. dd/MM/yyyy from .NET could be made to match what SQL Server is expecting without you altering the format strings.  However this could have other undesirable effects on your application since it would affect all locale-sensitive conversions.

    Yet another option is the SET DATEFORMAT command which you can run on an SqlCommand.ExecuteNonQuery to modify the month and day order for that particular SqlConnection. However, this can cause problems interoperating with any SQL Server code (such as stored procedures) that has not been designed to accomodate you changing the date format on the fly.

     

     

     

    Wednesday, April 21, 2010 12:39 AM
  • Well actually I have chosen to do the following :

     

    Date.ToString("s") into the queries. It works fine.

     

    For the SQL Injection problem, I have a wrapper that checks that kind of attacks and it should never append.

     

    About Thread.CurrentThread.CurrentCulture to a CultureInfo.GetCultureInfo that matches what mySQL Server is using, it's not possible because my app may be in many languages that the user can set.

     

    Thanks a lot for having helped me !

     

    Best regards.

     

    MatteoDev

    Wednesday, April 21, 2010 9:15 AM