C# to sql - date format

Question

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:

The dates in the textboxes are - 08-01-2013
In code behind dtFrom becomes - 1/8/2013 12:00:00 AM
The datatype for 'date' column is just date and not datetime

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.

What do dtFrom and dtTo contain - and what is the actual data type of the column for Date? Is it DateTime? — dash
– dash, Commented Jan 8, 2014 at 8:53

peter.petrov · Accepted Answer · 2014-01-08 09:08:21Z

1

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.

edited Jan 8, 2014 at 9:08

answered Jan 8, 2014 at 8:59

peter.petrov

39.6k24 gold badges114 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ruby Over a year ago

Changed it to DateTime as said. The only date in table is Jan 7. The SP works fine when I texted with ~execute GetData '2014-07-01','2014-07-01'~(yyyy-dd-mm). But from webpage, Even if i enter '08-01-2014' & '08-01-2014' (dd-mm-yyyy),in the textboxes, I get all records..!!!

peter.petrov Over a year ago

@Ruby Are you sure in your SP you have AND and not OR in the WHERE clause? Try adding more rows to your table and testing with them. Not just with 1 row.

Viral Maru · Accepted Answer · 2014-01-08 09:42:29Z

1

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 .

answered Jan 8, 2014 at 9:42

Viral Maru

335 bronze badges

Comments

th1rdey3 · Accepted Answer · 2014-01-08 09:53:16Z

1

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.

answered Jan 8, 2014 at 9:53

th1rdey3

4,4588 gold badges37 silver badges69 bronze badges

Comments

BenjV · Accepted Answer · 2014-01-08 09:52:58Z

0

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.

answered Jan 8, 2014 at 9:52

BenjV

1

Collectives™ on Stack Overflow

C# to sql - date format

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related