jqGrid filterToolbar - filter on DateTime using jquery ui datepicker

Question

I have been looking at filtering jqGrid by datetime using the filterToolbar.

My question is based on Olegs excellent answer here.

I finally figured out how to trigger toolbar search on date as follows:

colModel: [{
    name: 'RequestDate',
    index: 'RequestDate',
    formatter: 'date',
    formatoptions: {
        newformat: 'm/d/Y h:iA'
    },
    searchoptions: {
        sopt: ['eq'],
        dataInit: function (elem) {
            $(elem).datepicker({
                changeYear: true,
                changeMonth: true,
                onSelect: function (dateText, inst) {
                    setTimeout(function () {
                        $('#MyGrid')[0].triggerToolbar();
                    }, 50);
                }
            });
        }
    }
}]

Now when selecting the date from the picker I want to return all records for the given date ignoring the time.

I have tried updating the FilterObjectSet method with no luck. Has anyone been able to implement this successfully?

What I've tried: (see the code in Olegs linked solution)

Setting the FormatMapping to "(dateadd(dd,0, datediff(dd,0, it.{0})) = @p{1})" and addingSystem.DateTime to the switch statement:

case  "System.DateTime":
                        param = new ObjectParameter("p" + iParam, Convert.ToDateTime(rule.data));
                        break;

But this will result in a EntitySqlException:

'dateadd' cannot be resolved into a valid type or function.

Does anyone have a solution?

score 0 · Accepted Answer · answered Sep 05 '12 at 07:16

Ok figured it out this morning:

Added a new Operation:

de //de - date equals

Added a new string to FormatMapping that uses SqlServer.datediff:

"(SqlServer.datediff('DAY', it.{0} , @p{1}) = 0)" //de - date equals

and added the date case:

case  "System.DateTime":
    param = new ObjectParameter("p" + iParam, Convert.ToDateTime(rule.data));
break;

Changed sopt in colModel to sopt: ['de']

jqGrid filterToolbar - filter on DateTime using jquery ui datepicker

1 Answers1