SQL Trace/Profiler - Ambiguous date format being displayed (.NET Core/EF)

Question

We have a SQL Server 2008R2 instance installed with a language of English (United States). SSMS > Instance > Properties > General.

We have a Login set up with default language "British English". SSMS > Security > Logins > User > Properties.

The front end .NET Core application generates an insert statement (EF) via a form/Web API, which creates an "sp_executesql" system stored procedure using the Login set up above.

Using the SQL Server Profiler, I can see that the generated insert SQL contains string dates in the format 'yyyy-mm-dd'. For example "2019-01-15 10:59:19.410".

Prior to executing the statement there is an exec sp_reset_connection followed by the following SET statements (I believe this has something to do with connection pool sharing to ensure correct settings for each login).

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language British
set dateformat dmy
set datefirst 1
set transaction isolation level read committed

What is confusing me is that the statement runs successfully even though there are dates in the yyyy-mm-dd format whilst the environment is set up as British English (DMY). For example - "2019-01-15 10:59:19.410".

I have learnt that "2019-01-15 10:59:19.410" will be interpreted as 1st day of the 15th month 2019 if DMY is current setting.

For example this will fail as SQL will try to interpret date string as yyyy-dd-mm

SET LANGUAGE [British English];

SELECT
    CAST('2019-01-13' AS DATETIME);

Msg 242, Level 16, State 3, Line 3 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Setting language to English (us_english) changes string interpretation to MDY and the above statement works.

I am aware that I can use non ambiguous date strings but the issue I am having is related to the date strings being generated by the EF front end.

I can't work out why the statement is running successfully. Obviously there are implicit casts from date strings to DATETIME types.

If I copy the statement from SQL profiler and paste into SSMS and try to run against the same database/same user then it fails as I would expect.

Msg 8114, Level 16, State 1, Line 14 Error converting data type varchar to datetime.

So...

I understand what is going on when converting from string dates to DATETIME type and get consistent results running queries in SSMS but I can't understand why a query that fails in SSMS is running from the front end.

I apologise if this isn't very well explained.

Answer 1

What actually happens is that Entity Framework generates parameterized statements which are then passed to the server using the (binary) TDS protocol, which is how applications talk to SQL Server. DATETIME values passed as RPC parameters are not passed as strings -- they are passed as byte sequences that represent an exact value (for example, 2019-01-15T10:59:19.410 is represented as D6 A9 00 00 AF 16 B5 00 when sent over as a DATETIME (two little-endian integers). (Disclaimer: I haven't actually checked with a packet sniffer, so I may have gotten the bytes wrong).

When these parameterized statements are presented by SQL Profiler as if they were text batches, it reformats the values into strings because T-SQL has no native literal format for DATETIME values. Unfortunately it does so with a format that's not guaranteed to roundtrip under all possible language settings, which should be considered a bug in Profiler. All it's missing is a T, but that's an important omission.

Something similar happens with the mysterious exec sp_reset_connection calls you can see littering the traces of all applications that use connection pooling -- no application actually generates these calls, and if you try to execute this statement in Management Studio you'll see that there is no sp_reset_connection procedure. On the protocol level, there is just a "reset this connection" bit in the packet header that is translated to a fictitious call with fictitious SET statements representing the current options when this is presented as a statement or event trace.

In short: your applications are working fine, but if you want to replay exactly what happens when they're making calls, copy-pasting the Profiler output won't do.