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();
            }
        }

And why do you pass a varchar when the db expects a datetime? There is a DateTime type in C#. DateTime has no format thus you don't need any special conversion on the db side — Steve, Jun 10 '15 at 12:17
Indeed - you should avoid string conversions wherever possible, particularly for date/time values. Keep data in its most natural representation for as long as you can. It doesn't help that you haven't shown us *any* code or told us what your schema looks like. — Jon Skeet, Jun 10 '15 at 12:20
(1) Actually, You should avoid using DateTime in sql server as well, and use DateTime2, for lot of reasons starting of the storing size, precision, Range, flexibility, etc' (2) The DateTime class in C# does not behave like the DateTime type in sql server but more like the DateTime2. but it is mapped to DateTime type and should work for most cases (Not all! their rang of data is different for example). — Ronen Ariely, Jun 10 '15 at 12:33
To add some background to @Ronen's advice, [read this article.](http://blogs.msdn.com/b/cdnsoldevs/archive/2011/06/22/why-you-should-never-use-datetime-again.aspx) — Zohar Peled, Jun 10 '15 at 12:43
Just in case you're going to use string based dates somewhere, YYYYMMDD works in all cultures — James Z, Jun 10 '15 at 14:34
I don't use dates formatted as string in my c# code - I used the Sql Parameter to pass the parameters to the stored procedure. In the sql profiler I could see the formatted dates passed as strings, like 'yyyy-mm-dd ...' which my server doesnt recognize since the language is set to German and not English. I will post code later. — froggy, Jun 11 '15 at 05:59
@froggy That is odd because the YYYY-MM-DD ... format is culture insensitive, which is why .NET uses it. — Robert McKee, May 12 '16 at 18:00

score 4 · Answer 1 · edited May 23 '17 at 12:16

4

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.

edited May 23 '17 at 12:16

Community

1
1

answered Jun 10 '15 at 12:27

Zohar Peled

79,642
10
69
121

Dates and times do have a specific format in SQL Server, but it is not like the displaying format. It is UNDOCUMENTED. For example DATETIME is formated in the page file as 4 bytes for the date and 4 bytes for the time. Datetime2 is a bit more complex since it is flexible, but again it is formated. Knowing the format is important for lot of advance actions, for example, fixing corrupted database. – Ronen Ariely Jun 10 '15 at 13:20
We can actually read the binary information from the page file using the command DBCC PAGE and if we know the type's format, then we have the option to understand what we are reading. but again... this is all officially UNDOCUMENTED commands and information. – Ronen Ariely Jun 10 '15 at 13:20
As always, I learned something new when reading your responses. If you follow the link on the first line of my answer, you will see It's to an answer I've posted here that explains the way that sql server stores datetime values, so I'm aware of the fact that there is a storage format. (I've changed the text to be more accurate). However, I think it's probably a bit too advanced for most sql server users to play around with the data from the page directly. – Zohar Peled Jun 10 '15 at 13:28
thanks @Zohar Peled :-) **by the way, if you want to come, i am going to lecture at Microsoft in 3 weeks in the next Israeli User Group meeting.** – Ronen Ariely Jun 10 '15 at 13:44

score 1 · Answer 2 · answered Jun 10 '15 at 12:59

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 :-)

DateTime sent in wrong format to Sql Server 2014

2 Answers2