filter date with datatables in asp.net

Question

I need to filter date in column 'DATEATTEND' with datatables, and this is what i've been tried so far:

                 <div class="card-body">
                                <h4 class="card-title">DAILYDATA</h4>

                                <div class="table-responsive">
                                 <asp:TextBox ID="txtDate" runat="server" TextMode="Date" />
                                 <br />
                                 <br />
                                <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
                                <br />
                                <br />                               
                                 <asp:Repeater ID="rptrDAILYDATATemplate" runat="server">
                                    <HeaderTemplate>
                                      <table id="myTable"
                                        class="table table-striped table-bordered no-wrap" style="width:100%">
                                            <thead>
                                                <tr>
                                                    <th>NIP</th>
                                                    <th>NAME</th>
                                                    <th>DEPARTMENT</th>
                                                    <th>DATEATTEND</th>
                                                    <th>STATUSTYPE</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                    </HeaderTemplate>
                                    <ItemTemplate>
                                        <tr>

                                            <td><%# DataBinder.Eval(Container.DataItem, "NIP") %></td>
                                            <td><%# DataBinder.Eval(Container.DataItem, "NAME")%></td>
                                            <td><%# DataBinder.Eval(Container.DataItem, "DEPARTMENT") %></td>
                                            <td><%# DataBinder.Eval(Container.DataItem, "DATEATTEND") %></td>
                                            <td><%# DataBinder.Eval(Container.DataItem, "STATUSTYPE") %></td>
                                        </tr>
                                    </ItemTemplate>
                                    <FooterTemplate>

                                            </tbody>
                                        </table>
                                    </FooterTemplate>
                                  </asp:Repeater>
                                </div>
                            </div>

---

   protected void Button1_Click(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["MANHOURConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(CS);
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM DAILYDATA WHERE LEFT(DATEATTEND,11)=@DATEATTEND", con))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {


                    cmd.Parameters.AddWithValue("@DATEATTEND", txtDate.Text);
                    con.Open();

                    DataTable dtDaily = new DataTable();


                    sda.Fill(dtDaily);


                    rptrDAILYDATATemplate.DataSource = dtDaily;
                    rptrDAILYDATATemplate.DataBind();
                    con.Close();
                }
            }

        }

when i run it, it seems working... but my problem is when i filter that according to the date in my database it's says 'no data available' and i'm sure i have that date in my database. Why it's not working?

---

the column DATEATTEND has a datetime datatype, for example the value in that column 2020-03-21 08:00:00 and i just wanna take 2020-03-21 only, so i used LEFT function.

Answer 1

The problem is likely that you are using LEFT on a datetime datatype. LEFT is for strings not date and time data types. Using LEFT on other data types has "odd" (but expected) results. For example:

SELECT LEFT(CONVERT(datetime,'20190317'),11) dt_1,
       LEFT(CONVERT(date,'20190317'),11) d_1,
       LEFT(CONVERT(datetime2,'20190317'),11) dt2_1,
       LEFT(CONVERT(datetime,'2019-03-17T16:12:19.134'),11) dt_2,
       LEFT(CONVERT(datetime2,'2019-03-17T16:12:19.134'),11) dt2_2;

This returns:

dt_1        d_1        dt2_1        dt_2        dt2_2
----------- ----------- ----------- ----------- -----------
Mar 17 2019 2019-03-17  2019-03-17  Mar 17 2019 2019-03-17 

This is likely having unexpected results.

The "proper" way top ensure you get all the values for a date is using >= and < logic:

SELECT *
FROM DAILYDATA
WHERE DATEATTEND >= @DATEATTEND
  AND DATEATTEND < DATEADD(DAY,1,@DATEATTEND);