Date Time format problem in sql server

Question

I have an application in asp.net which worked fine untill recently when i changed the datetime format in Regional and Language Settings in Control Panel.

The date time format was default when i installed XP. I chose Indian standard time while installing XP.

I changed the date time format to dd/MM/yyyy HH:mm:ss. And my application started to throw an exception whenever i tried to insert any datetime in to the table.

The exception i get is:

System.Data.SqlClient.SqlException: Error converting data type varchar to datetime.

Please help me on this

Answer 1

Hard to know exactly what's going on without seeing the code that's throwing. However, if you need to communicate dates to SQL Server, it is generally good practice to use the ISO 8601 standard for representation because it is unambiguous and locale-independent. The most important formats are:

• yyyy-MM-dd for dates
• hh:mm:ss for time
• yyyy-MM-dd hh:mm:ss for date/time

My guess is that you have a query that's sending over dates in the current locale, and the locale on the server does not match.

Edit: And for the record, this doesn't preclude anything that Rob said in his answer, i.e. try to avoid passing hard-coded dates or hard-coded SQL at all. This only applies if you need to for some reason.

Edit 2: I've been informed that the yyyy-MM-dd format can still be wrong for some locales; so instead of this, if you need to pass in a literal date string, you should instead use yyyyMMdd.

Answer 2

As per my comment, you'll probably want to make sure you're using code that behaves in a similar way to the code below (i.e. using parameters rather than string concatenation)

        var myConnectionString = "connection string goes here";
        var myDateValue = DateTime.Now;
        using (var connection = new SqlConnection(myConnectionString))
        {
            using (var command = new SqlCommand("SELECT COUNT(1) FROM dbo.table WHERE datecolumn = @datevalue", connection))
            {
                var dateValueParameter = new SqlParameter("@datevalue", myDateValue);
                command.Parameters.Add(dateValueParameter);

                var result = Convert.ToInt32(command.ExecuteScalar());
            }
        }

Answer 3

Try adding "Current Language=YourLanguage" to the SQL server connection string. Where YourLanguage is the language you want SQL to use when reading values such as the dates.

You can see a list of all languages supported by SQL by executing the following SQL command:

select * from master.dbo.syslanguages