CODEWITHSUNDEEP
c#asp.netasp.net-mvcdatetimesql-server-2014
Stefano Guimarães Falce
Stefano Guimarães Falce

Reputation: 125

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

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

Upvotes: 1

Views: 384

Answers (2)

Mohammad
Mohammad

Reputation: 2764

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;

Upvotes: 1

Alkis Giamalis
Alkis Giamalis

Reputation: 320

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

Upvotes: 0

Related Questions