ObjectDataSource filtering on date/time in SPGridView

Question

I'm scratching my head on this one... I have date/time columns which I want to sort by date/time but when filtered, only filter by the date part of the date/time. My filter code (a lambda contained within a setup method) looks like this:

        this.taskGrid.AllowFiltering = true;
        this.odsGrid.Filtering += (sender, e) =>
        {
            var odsv = (ObjectDataSourceView)sender;
            var filterExp = odsv.FilterExpression;

            if (filterExp.Contains("Date")
                || filterExp.Contains("Deadline")
                || filterExp.Contains("Client Proof"))
            {
                var fieldAndVal = filterExp.Split('=');
                DateTime date;

                if (DateTime.TryParse(fieldAndVal[1].Replace("'", string.Empty), out date))
                {
                    odsv.FilterExpression = "("
                        + fieldAndVal[0] + " >= '" + date
                        + "') AND ("
                        + fieldAndVal[0] + " < '" + date.AddDays(1) + "')";
                }
            }

            this.ViewState["FilterExpression"] = odsv.FilterExpression;
        };

So what this does is changes an expression which looks like "[Client Proof] = '8/5/2010 4:24:44 PM'" and rewrites it as "([Client Proof] >= '8/5/2010') AND ([Client Proof] < '8/6/2010')".

Now here's the kicker, it works in my development environment (Win2K3 32-bit, MOSS 2K7) but once I promote the solution into the QC environment (Win2K8R2 64-bit, MOSS 2K7), performing the same filter results in an empty grid. Does anyone have any ideas? Or a good way to see what's actually happening when the filter is applied? Thanks!

ETA:

As it turns out, by the time it gets to the Filtering event, the filter format of [{1}] = '{0}' has already been applied and all that stuff I'm doing isn't doing anything. The previous developers treated the date as a string and used LIKE, which worked all right but then sorting was bonkers. So, sorting as a date/time and filtering like a string would be what I'd like but can't seem to bring the two together.

Answer 1

I thought of something after a while. Could you generate a new column that contains only the date portion from the full DateTime column? Then, you would apply the filter to the column that contains only the date portion.

You might try setting the BoundField.DataField to your DateOnly column.

P.S. Not sure why it is working in one environment and not in another. I'm guessing a data problem / difference.