DateTime format issue on StoredProcedure - C# ASP.NET x MS SQL Server 2014

Question

I'm facing an issue while running an SQL Server 2014 stored procedure from my C# ASP.NET application (MVC 3). When I manually run this procedure with the same parameters, it returns me all proper results. When the procedure is executed from the C# application, the "reader" comes empty, without any data. It seems to be an issue on the DateTime format. What should I do to solve it? Here is the code:

SQL:

ALTER PROCEDURE [dbo].[my_proc]
(
    @client_code AS varchar(7),
    @begin_date AS datetime,
    @end_date AS datetime
)
AS
BEGIN
    SET NOCOUNT ON; 


SELECT *
    from [dbo].[my_table] c
    WHERE c.code = @client_code
    AND c.date1 >= @begin_date
    AND c.date2 <= @end_date

END

C#:

DateTime Today = DateTime.Today;
DateTime LastYear = Today.AddYears(-1);
sql = new SqlConnection(/*my string connection*/);
sql.Open();
adapter.SelectCommand = new SqlCommand();
adapter.SelectCommand.CommandText = "dbo.my_proc";
adapter.SelectCommand.Connection = sql;
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

param1 = adapter.SelectCommand.Parameters.Add("@client_code", SqlDbType.VarChar);
param1.Value = cod_client;

param2 = adapter.SelectCommand.Parameters.Add("@begin_date", SqlDbType.Date);
param2.Value = LastYear.ToString("yyyy-MM-dd");

param3 = adapter.SelectCommand.Parameters.Add("@end_date", SqlDbType.Date);
param3.Value = Today.ToString("yyyy-MM-dd");

reader = adapter.SelectCommand.ExecuteReader();

`.ToString("...")`? No, you will have problems. Use `SqlDbType.DateTime` and use `Today.Date` if you want to drop the time part — Adriano Repetti, May 08 '17 at 12:46
You expect parameters of type DateTime, why do you convert your perfectly fit datetime variables to strings? — Steve, May 08 '17 at 12:47
I was afraid of a datetime format mismatch between C# and SQL Server, so I tried to manage the format through toString method. — Stefano Guimarães Falce, May 08 '17 at 12:50

score 1 · Accepted Answer · edited May 10 '17 at 07:04

1

You shouldn't convert DateTime to string. just pass it "as-is" to the stored procedure. If you want just date part then use Date. Remember do not change the data type.

param2 = adapter.SelectCommand.Parameters.Add("@begin_date", SqlDbType.Date);
param2.Value = LastYear.Date;

param3 = adapter.SelectCommand.Parameters.Add("@end_date", SqlDbType.Date);
param3.Value = Today.Date;

edited May 10 '17 at 07:04

marc_s

732,580
175
1,330
1,459

answered May 08 '17 at 12:47

Mohammad

2,724
6
29
55

I tried this way and it's not working as expected yet. I'm afraid of a datetime format mismatch. – Stefano Guimarães Falce May 08 '17 at 12:52
I'm using this way for long time. would you please explain what went wrong? – Mohammad May 08 '17 at 12:55
The procedure is returning empty when executed from the C# application. However, when I manually run the procedure directly from SQL Server with the same parameters, all the proper rows are returned. – Stefano Guimarães Falce May 08 '17 at 12:59
i don't think the DateTime is the problem. please insert the input DateTime in a table in order to trace the problem. – Mohammad May 08 '17 at 13:10
Can you use casting and try once? like cast(c.date1 as date) >= cast(@begin_date as date) – divya May 08 '17 at 14:12

score 0 · Answer 2 · answered May 08 '17 at 12:52

0

Try the AddWithValue method wich is less verbose and can identify the DataType of the parameter on the fly:

        adapter.SelectCommand.Parameters.AddWithValue("@client_code", cod_client);
        adapter.SelectCommand.Parameters.AddWithValue("@begin_date", LastYear.Date);
        adapter.SelectCommand.Parameters.AddWithValue("@end_date", Today.Date);

answered May 08 '17 at 12:52

Alkis Giamalis

300
7
14

1

This doesn't tackles on the root problem (sending date values as text) and most important [AddWithValue is a bad practice anyway](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). – Alejandro May 08 '17 at 13:54

DateTime format issue on StoredProcedure - C# ASP.NET x MS SQL Server 2014

2 Answers2