SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

Question

I have a connected SQL Server database in Visual Studio and am displaying its content in a grid. I created a dropdown menu with the column names as selectable options and a text field to filter for specific content, e.g., DropDown = "Start" - Textfield = 14.03.2015 = Filter Column "Start" for each entry that contains "14.03.2015" - and display it in the Grid.

I'm basically done with that part. The only problem left that I'm facing is whenever I enter a date - e.g., 14.03.2015 it only displays dates which start at 00:00:00 - the other entries that do not start at 00:00:00 are ignored and I can't figure out how to modify this to work properly.

The Grid looks like this: http://abload.de/img/untitled123yqkyn.png

And I am using the following C# code to filter:

protected void Button1_Click(object sender, EventArgs e)
{
    string FilterExpression = string.Empty;

    if (DropDownList1.SelectedValue.ToString().Equals("Start"))
    {
        FilterExpression = string.Format("Start  = '{0}'", TextBox1.Text);
    }
    else if (DropDownList1.SelectedValue.ToString().Equals("End"))
    {
        FilterExpression = string.Format("End  = '{0}'", TextBox1.Text);
    }
    else if (DropDownList1.SelectedValue.ToString().Equals("Creation Time"))
    {
        FilterExpression = string.Format("DateTimeCreated = '{0}'", TextBox1.Text);
    }
    else if (DropDownList1.SelectedValue.ToString().Equals("Last Modified"))
    {
        FilterExpression = string.Format("LastModifiedTime = '{0}'", TextBox1.Text);
    }
    else
    {
        FilterExpression = string.Concat(DropDownList1.SelectedValue, " Like '%{0}%'");
    }

    SqlDataSource1.FilterParameters.Clear();
    SqlDataSource1.FilterParameters.Add(new ControlParameter(DropDownList1.SelectedValue, "TextBox1", "Text"));
    SqlDataSource1.FilterExpression = FilterExpression;
}

This is my Grid:

Organizer Room Creation Time Start End Last Modified  

    <asp:TextBox ID="TextBox1" runat="server" Width="315px"></asp:TextBox> &nbsp;
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search" Width="100px"/> &nbsp;
    <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Reset Search" Width="100px"/>
    <br/>
    <br/>

    <asp:GridView ID="GridView1" runat="server" BorderColor="#F9F9F9" BorderStyle="Solid" CellPadding="4" ForeColor="#333333" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" AllowSorting="True"  pagesize="1000" AllowPaging="True" HorizontalAlign="Center">
    <AlternatingRowStyle BackColor="White"/>

        <Columns>
        <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" ItemStyle-HorizontalAlign="Center"/>
        <asp:BoundField DataField="Organizer" HeaderText="Organizer" SortExpression="Organizer" ConvertEmptyStringToNull="False" HtmlEncode="False" HtmlEncodeFormatString="False" InsertVisible="False"/>
        <asp:BoundField DataField="Room" HeaderText="Room" SortExpression="Room"/>
        <asp:BoundField DataField="DateTimeCreated" HeaderText="Creation Time" SortExpression="DateTimeCreated"/>
        <asp:BoundField DataField="Start" HeaderText="Start" SortExpression="Start" />
        <asp:BoundField DataField="End" HeaderText="End" SortExpression="End"/>
        <asp:BoundField DataField="LastModifiedTime" HeaderText="Last Modified" SortExpression="LastModifiedTime" />
        <asp:CheckBoxField DataField="Cancelled" HeaderText="Cancelled" SortExpression="Cancelled" ItemStyle-HorizontalAlign="Center" />
        </Columns>

    <EditRowStyle BackColor="#2461BF"/>
    <FooterStyle BackColor="#E1000F" Font-Bold="True" ForeColor="White"/>
    <HeaderStyle BackColor="#E1000F" Font-Bold="True" ForeColor="White" Font-Underline="false"/>
    <PagerStyle BackColor="#E1000F" ForeColor="White" HorizontalAlign="Center"/>
    <RowStyle BackColor="#F9F9F9"/>
    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333"/>
    </asp:GridView>

    <asp:SqlDataSource ID="xyz" runat="server" ConnectionString="<%$ ConnectionStrings:VCConnectionString %>" SelectCommand="SELECT * FROM [xyz]"></asp:SqlDataSource>
 </center>

Please excuse the quality of the code, I'm completely new to C# and for that matter programming in itself. I hope that there is someone able to help me.

Answer 1

What happens if you change all of the filters to use 'LIKE':

if (DropDownList1.SelectedValue.ToString().Equals("Start"))
{
    FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text);
}

Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date.

Update

Or perhaps you could try this...

if (DropDownList1.SelectedValue.ToString().Equals("Start"))
{
    FilterExpression = string.Format("Start >= '{0} 0:00:00' AND Start <= '{1} 23:59:59'", TextBox1.Text, TextBox1.Text);
}

Update 2

... Or if you really want to make sure you get all records from that final second of 23:59:59, you could use this (I think)...

if (DropDownList1.SelectedValue.ToString().Equals("Start"))
{
    FilterExpression = string.Format("Start >= '{0} 0:00:00' AND Start < DATEADD(day,1,'{1}')", TextBox1.Text, TextBox1.Text);
}

Notice that the version looks for records less than your given date "plus one day".

Personally, I would use the "Update 1" script, and compare the dates against 23:59:59, even though, yes, it would ignore records created in that final second of the day (eg at 23:59:59.403).

Answer 2

The problem is due to the fact you're filtering on a datetime value; if you set only the date part the system interprets it as at midnight. Try to pass also the time value and it will work.

EDIT: The problem is due the conversion from string and datetime:

   else
    {
        FilterExpression = string.Concat(DropDownList1.SelectedValue, " Like '%{0}%'");
    }

Perhaps changing your logic to use the value as a dateime should work. In the folowing example the first doesn't work, the second one does:

SELECT * 
FROM [Events]
WHERE EventDate LIKE '%2012-06-08%'
SELECT * 
FROM [Events]
WHERE EventDate >= '2012-06-08' AND EventDate <= '2012-06-08 23:59:59'