How to specify date format used by ADO.NET when passing it to a stored procedure?

Question

I am using ADO.NET to pass a datetime parameter to a stored procedure and I am getting

Error converting data type varchar to datetime

on servers configured with DATEFORMAT ydm.

I have a simple stored procedure (it just takes a parameter and do nothing):

CREATE PROCEDURE [dbo].[TestDate]
    @date AS datetime
AS
BEGIN
    RETURN 0
END

I am executing it with ADO.NET by using datetime parameter:

Dim param As SqlParameter = New SqlParameter()
param.SqlDbType = SqlDbType.DateTime
param.Value = New Date(2021, 1, 13)

Execution on the SQL side looks like this:

EXEC sp_executesql N'EXEC dbo.TestDate @date = @date', N'@date datetime', @date = '2021-01-13 00:00:00.000'

and it works on most of our servers. Unfortunately one of our clients has different configuration. I have simulated this by adding SET DATEFORMAT ymd to previous execution, so when it runs it throws

Error converting data type varchar to datetime

SET DATEFORMAT ydm;

EXEC sp_executesql N'EXEC dbo.TestDate @date = @date', N'@date datetime', @date = '2021-01-13 00:00:00.000'

It seems like ADO.NET passes dates in a ODBC format which is sensitive to dateformat / language setting. Do you have some idea how to overcome this issue? (I have tried passing dates as strings in ISO 8601 format and it works with this example, but some of our other queries have other issues with that and I don't like this kind of "dirty tricks").

Edit:

Thanks for replies, I have checked sp code once more. I have further simplified the example: I have captured execution of real procedure with profiler on the client server and I have deleted all "noise code". On client environment (from application and Management Studio) I am getting

Error converting data type varchar to datetime

(which can be simulated by setting dateformat: SET DATEFORMAT dmy - I have checked it from DBCC USEROPTIONS):

exec sp_executesql N'',N'@date datetime',@date='2021-01-27 10:04:55.263'

Answer 1

Dates have no format, they're binary values. In .NET DateTime uses a tick count field internally. SQL Server uses several date types, none of which is based, parsed or stored as a string. ADO.NET passes parameters as separate, binary parameters to the RPC call to the server.

One way or the other the data access code that's missing is converting dates to strings instead of using parameterized queries.

This code wouldn't convert dates to strings or need parsing on the server:

Using con As SqlConnection = New SqlConnection(connectionString)
Using cmd As New SqlCommand("TestDate", con)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@date", SqlDbType.Date).Value = New Date(2021, 1, 13)

cmd.ExecuteNonQuery()

The type could be any of the date types, eg: SqlDbType.DateTime, SqlDbType.DateTime2 or SqlDbType.DateTimeOffset. The type that best matches the stored procedure parameter is Date

What about SSMS ?

In SSMS the queries are written as text. Even a parameter declaration in the end uses text. You need to provide the correct date literal to avoid localization issues.

datetime is a legacy type that's affected by DATEFORMAT unless a full ISO8601 string or an unseparated YYYYMMDD string is used. This means that YYYY-MM-DD can be parsed as YYYY-DD-MM if datetime is used.

The newer types don't have this problem. date, the correct type for this parameter and datetime2, datetimeoffset all recognize YYYY-MM-DD no matter what DATEFORMAT is.