DateTime sent in wrong format to Sql Server 2014

Question

I'd like to execute a stored procedure on an sql server 2014. The sql server is set up in German, the user used for connecting to the sql server has also configured German as language. If I try to execute the sql procedure or raw sql, I always get the error

varchar cannot be converted to datetime

even if I provide german datetime values. I've found out that it works if I prepend the sql text with the command SET DATEFORMAT dmy.

The problem is the same for ADO .NET as well as Entity framework. Setting the thread and ui culture to German also didn't help.

It seems that C# SQL Connection sets the culture to default (English) independently of thread culture, date format or sql server language.

Any ideas highly appreciated how to set the culture correctly - such that I don't need to send always SET DATEFORMAT dmy before the real sql text.

UPDATE

This is my code to call the sql stored procedure and pass the dates using the c# sql parameter.

        SqlConnection sqlConnection = null;
        try
        {
            // open connection to the database
            sqlConnection = new SqlConnection(Convert.ToString(ConfigurationManager.ConnectionStrings[ProductivityAnalyzerDatabase.ConnectionStringName]));
            sqlConnection.Open();

            // setup command
            var sqlCommand = new SqlCommand("UpdateEmployeeBalances", sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlCommand.Parameters.Add(new SqlParameter("@employeeId", employeeId));
            sqlCommand.Parameters.Add(new SqlParameter("@startDate", startDate));
            sqlCommand.Parameters.Add(new SqlParameter("@endDate", endDate));

            sqlCommand.ExecuteNonQuery();
        }
        finally
        {
            if (sqlConnection != null && sqlConnection.State == ConnectionState.Open)
            {
                sqlConnection.Close();
            }
        }

Answer 1

Date values are not stored with their display format.
The problem is that you send your dates to Sql Server as strings, thus forcing sql server to cast the strings to date values. unless you send your dates in ANSI-SQL format (yyyy-mm-dd) this casting might fail or yield unexpected results (is 04/02/2015 April 2nd or February 4th?)

The correct solution, as Steve mentioned in his comment, is to use c#'s DateTime structure as the value of the parameter for the stored procedure. (don't use ToString or anything like that.)
Note that the parameter should be declared as a date type (datetime, datetime2, or date) in the stored procedure itself.

Answer 2

Good day,

You can read more about this issue in this clog: http://ariely.info/Blog/tabid/83/EntryId/161/Date-displaying-format-vs-Date-storing-format.aspx

in short (from the link above):

Implicit conversion of ambiguous date formats are interpreted according to the language of the connection or the collate of the query. Always keep and following rules, in order to make your work more compatible.

• Using .Net you should use a type that is mapped correctly to the SQL Server types. Check this link: https://msdn.microsoft.com/en-us/library/cc716729%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

• When you specify dates in DML queries, always use constants way that are interpreted the same way for all language settings!

  for example use format yyyymmdd as 20160227, Use explicit CONVERT statement with an explicit style parameter, Use escape clauses while using ADO, OLE DB, or ODBC.

• Remember that those are only display formats. In the database the data stored in the same way, no matter what is your language!

  *datetime is stored as 4 bytes for the date + 4 bytes for the time, Datetime2 is a bit more complex since it is flexible. you can read more on the undocumented internal stored format here.

I hope this is useful :-)