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.

You should remove one tag between mysql and sql-server, they are clashing — Simone, Jun 22 '15 at 07:44

Mike Gledhill · Accepted Answer · 2015-06-22T14:55:49.120

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

edited Jun 22 '15 at 14:55

answered Jun 22 '15 at 08:16

Mike Gledhill

23,658
6
133
143

Hi. When i do that, i get an exception: Cannot perform 'Like' operation on System.DateTime and System.String. – Andre Jun 22 '15 at 08:18
You saved my day. Thank you a lot! That worked great. – Andre Jun 22 '15 at 08:22
@Andre - as I pointed out on the other answer - unless you want to exclude events that happen *during* the final second of the day, I'd recommend computing the next days date and using ` – Damien_The_Unbeliever Jun 22 '15 at 08:25
This would be easier: if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start >= '{0} 0:00:00' AND Start <= '{0} 23:59:59'", TextBox1.Text); } – Simone Jun 22 '15 at 08:30
Thanks everyone. The code works great on Start and End. On LastModifiedTime and DateTimeCreated it seems to ignore most entries though. Does anyone have a clue why that could happen? – Andre Jun 22 '15 at 08:49
Perhaps the format of the dates you're entering? Out of interest, type in dates in your textboxes in the format: 2015-12-31, and see if it makes a difference. My suggestions above made no effort to reformat the textbox strings into any kind of date format ("yyyy-mm-dd"), so perhaps this is producing incorrect SQL results. – Mike Gledhill Jun 22 '15 at 09:00
The format seems to be correct. But what is wierd is that i only get results with DateTimeCreated and LastModifiedTime, when Start is the same date as those. Like it checks with the first if and only compares to the else if and then shows only matching results between the two. – Andre Jun 22 '15 at 09:05
Put a breakpoint in your code, and check the "DropDownList1.SelectedValue" value. Is it really picking up the right value from your drop down list ? And can you find out the "FilterExpression" value which gets evaluated ? – Mike Gledhill Jun 22 '15 at 09:17
Hi, i've tried to do so. I never worked with Breakpoints before though. Where would i set the Breakpoint in my Code and how do i read out the value? – Andre Jun 22 '15 at 10:14
1

I managed to fix my mistake. It was my own stupidity which caused the bug. Thank you for the effort. It's greatly appreciated! – Andre Jun 22 '15 at 10:23
You prefer "Update 1", even though you know that it can exclude certain values? In a way that's non-obvious to a casual inspection. That screams "bug that will lurk in the code base for months or years and then take hours or days to track down". – Damien_The_Unbeliever Jun 22 '15 at 14:46

Simone · Answer 2 · 2015-06-22T08:13:49.593

0

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'

edited Jun 22 '15 at 08:13

answered Jun 22 '15 at 07:46

Simone

1,720
1
11
19

Hi, thank you for your answer. I know that this will work. What i want is for it to show all entrys of that day. – Andre Jun 22 '15 at 07:48
`EventDate <= '2012-06-08 23:59:59'` is a bad idea - unless it was your intention to exclude events that happened *during* the final second of the day (e.g. at 23:59:59.423). It's far *simpler* to use an exclusive endpoint for the comparison - `EventDate < '2012-06-09'` – Damien_The_Unbeliever Jun 22 '15 at 08:21
@Damien_The_Unbeliever You're right but i thought it was clearer to have the time visualized, for this reason i didn't adapt the code on his needings. Now I've seen someone just copied the answer to use my suggestion in an improper way :( – Simone Jun 22 '15 at 08:28

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

2 Answers2