CODEWITHSUNDEEP
c#sqlsql-server-2008-r2
Ruby
Ruby

Reputation: 969

C# to sql - date format

Table - tblData (Data for Jan.):

This is how I see the data with select * from tblData

ID  Tag     Date
1   OB    2014-07-01
2   OC    2014-07-01
3   AB    2014-07-01

Trying to achieve the same from C#, but this is not working.

DateTime dtFrom = Convert.ToDateTime(txtFromDate.Text.Trim(),System.Globalization.
                  CultureInfo.GetCultureInfo("hi-IN").DateTimeFormat);

DateTime dtTo = Convert.ToDateTime(txtToDate.Text.Trim(),System.Globalization.
                CultureInfo.GetCultureInfo("hi-IN").DateTimeFormat);

gridview1.DataSource = objDAL.GetData(dtFrom,dtTo);
gridview1.DataBind();

Here's the SQL Server 2008 R2 stored procedure

    Alter Procedure GetData
    (
      @DateFrom Date, @DateTo Date
    )  
    As
    Begin
        Select * from tblData
        where date between @DateFrom and @DateTo
    End

No rows are returned with the above query, and I believe this could be due to the date formats. How can this be solved? Not sure if it has to be changed in code or in the stored procedure.

EDIT:

GetData in DAL

public DataTable GetData(DateTime dtfrom, DateTime dtto)
{
  var qry = from p in MyDB.GetData(dtfrom,dtto)
            select new
             {
               id = p.id,
               tag = p.tag,
               date = p.date
             }
  dt = qry.ToDataTable();
  return dt;
}

The problem's not with the dal code. It executes fine. The row count is 0. So I'm sure its the diff. date formats.

Upvotes: 0

Views: 420

Answers (4)

th1rdey3
th1rdey3

Reputation: 4358

When calling your procedure inside MyDB.GetData(dtFrom,dtTo) pass dtFrom and dtTo as strings. For example -

//I am assuming you are using SqlCommand To connect to DB
cmd.Parameters.AddWithValue("@DateFrom", dtFrom.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@DateTo", dtFrom.ToString("yyyy-MM-dd"));

OR,

cmd.Parameters.AddWithValue("@DateFrom", dtFrom.ToString("dd-MMM-yyyy"));
cmd.Parameters.AddWithValue("@DateTo", dtFrom.ToString("dd-MMM-yyyy"));

sql server can read this string values and convert them to date correctly.

Upvotes: 1

BenjV
BenjV

Reputation: 1

Check the date format for your query in both C# and in SQL. Sometimes the problem occurs when there's a mismatch of date format, a common mistake is that either of the query includes time and the other does not.

Upvotes: 0

Viral Maru
Viral Maru

Reputation: 33

Use can use CONVERT function for converting the date as below..

Alter Procedure GetData
(
  @DateFrom Date, @DateTo Date
)  
As
Begin
  set @DateFrom = CONVERT(DATE, @DateFrom, 101)
  set @DateTo = CONVERT(DATE, @DateTo , 101)

  Select * from tblData
  where [date] between @DateFrom and @DateTo
End

for more date conversation format use this link .

Upvotes: 1

peter.petrov
peter.petrov

Reputation: 39437

1) Try this in the SP. 

Select * from tblData
Where
( [date] >= @DateFrom and [date] <= @DateTo )

See if it helps.

2) Change your SP to accept DateTime parameters, otherwise somebody (DAL or below) is probably implicitly converting the DateTime values from the UI to Date values.

Upvotes: 1

Related Questions