Filtering jqGrid datetime columns using date picke

Question

I currently have an issue where I have datetime columns in a grid that are formatted to only display the date portion of the field. So the raw data looks like "2015-04-15T15:31:49.357" and the grid column looks like "4/15/2015".

I am using a datepicker to support column filtering and would like to be able to use the "eq" operator to filter using "equals" but just on the date portion. Currently I do not get any matches because the time is getting in the way.

Is it possible to get around this? I know I can manipulate the raw data to strip off the time portion of the date, but I would prefer to keep that information in the raw data since I also support exporting to excel and the time might be needed.

My current options for the column are:

          formatter = "date";
          formatoptions = {srcformat: "Y-m-d", newformat: "n/j/Y"};

I have tried various options but so far have not had any luck.

I am also using the free-jqgrid fork.

Answer 1

Response from OlegKi on github: https://github.com/free-jqgrid/jqGrid/issues/50

I introduced custom filtering feature in free jqGrid to allow easy implements the scenarios like youth. The answer provide an example of such implementation.

In your case one can define new Date only \"equal\"compare operation under the short name "deq" for example and the compare operation Date only "not equal" under the short name dne. The code of customSortOperations option could be the following:

customSortOperations: {
deq: {
    operand: "==",
    text: "Date only \"equal\"",
    filter: function (options) {
        var p = this.p, iCol = p.iColByName[options.cmName], cm = p.colModel[iCol],
            newformat = cm.formatoptions != null && cm.formatoptions.newformat ?
                    cm.formatoptions.newformat :
                    $(this).jqGrid("getGridRes", "formatter.date.newformat"),
            srcformat = cm.formatoptions != null && cm.formatoptions.srcformat ?
                    cm.formatoptions.srcformat :
                    $(this).jqGrid("getGridRes", "formatter.date.srcformat"),
            fieldData = $.jgrid.parseDate.call(this, srcformat, options.item[options.cmName]),
            searchValue = $.jgrid.parseDate.call(this, newformat, options.searchValue);
        return fieldData.getFullYear() === searchValue.getFullYear() &&
            fieldData.getMonth() === searchValue.getMonth() &&
            fieldData.getDate() === searchValue.getDate();
    }
},
dne: {
    operand: "!=",
    text: "Date only \"not equal\"",
    filter: function (options) {
        var p = this.p, iCol = p.iColByName[options.cmName], cm = p.colModel[iCol],
            newformat = cm.formatoptions != null && cm.formatoptions.newformat ?
                    cm.formatoptions.newformat :
                    $(this).jqGrid("getGridRes", "formatter.date.newformat"),
            srcformat = cm.formatoptions != null && cm.formatoptions.srcformat ?
                    cm.formatoptions.srcformat :
                    $(this).jqGrid("getGridRes", "formatter.date.srcformat"),
            fieldData = $.jgrid.parseDate.call(this, srcformat, options.item[options.cmName]),
            searchValue = $.jgrid.parseDate.call(this, newformat, options.searchValue);
        return fieldData.getFullYear() !== searchValue.getFullYear() ||
            fieldData.getMonth() !== searchValue.getMonth() ||
            fieldData.getDate() !== searchValue.getDate();
    }
}

} To be able to use new "deq" and "dne" operation you should include there in sopt of searchoptions of the column with the date.

The demo uses the above code. The input data contains 3 dates: "2015-04-15T15:31:49.357", "2015-04-15T21:15:40.123", "2015-04-15". The filtering by 15-Apr-2015 display all the three rows.

Another demo uses practically the same code, but displays the date in full date/time format. Nevertheless the filtering works. Be carefully, that I use the latest free jqGrid sources from GitHub in the demo. It's really needed because I made some small changes in the code of parseDate to make the demo working.

Answer 2

I introduced custom filtering feature in free jqGrid to allow easy implements the scenarios like youth. The answer provide an example of such implementation.

In your case one can define new Date only "equal"compare operation under the short name "deq" for example and the compare operation Date only "not equal" under the short name dne. The code of customSortOperations option could be the following:

customSortOperations: {
    deq: {
        operand: "==",
        text: "Date only \"equal\"",
        filter: function (options) {
            var p = this.p, iCol = p.iColByName[options.cmName], cm = p.colModel[iCol],
                newformat = cm.formatoptions != null && cm.formatoptions.newformat ?
                        cm.formatoptions.newformat :
                        $(this).jqGrid("getGridRes", "formatter.date.newformat"),
                srcformat = cm.formatoptions != null && cm.formatoptions.srcformat ?
                        cm.formatoptions.srcformat :
                        $(this).jqGrid("getGridRes", "formatter.date.srcformat"),
                fieldData = $.jgrid.parseDate.call(this, srcformat, options.item[options.cmName]),
                searchValue = $.jgrid.parseDate.call(this, newformat, options.searchValue);
            return fieldData.getFullYear() === searchValue.getFullYear() &&
                fieldData.getMonth() === searchValue.getMonth() &&
                fieldData.getDate() === searchValue.getDate();
        }
    },
    dne: {
        operand: "!=",
        text: "Date only \"not equal\"",
        filter: function (options) {
            var p = this.p, iCol = p.iColByName[options.cmName], cm = p.colModel[iCol],
                newformat = cm.formatoptions != null && cm.formatoptions.newformat ?
                        cm.formatoptions.newformat :
                        $(this).jqGrid("getGridRes", "formatter.date.newformat"),
                srcformat = cm.formatoptions != null && cm.formatoptions.srcformat ?
                        cm.formatoptions.srcformat :
                        $(this).jqGrid("getGridRes", "formatter.date.srcformat"),
                fieldData = $.jgrid.parseDate.call(this, srcformat, options.item[options.cmName]),
                searchValue = $.jgrid.parseDate.call(this, newformat, options.searchValue);
            return fieldData.getFullYear() !== searchValue.getFullYear() ||
                fieldData.getMonth() !== searchValue.getMonth() ||
                fieldData.getDate() !== searchValue.getDate();
        }
    }
}

To be able to use new "deq" and "dne" operation you should include there in sopt of searchoptions of the column with the date.

The demo uses the above code. The input data contains 3 dates: "2015-04-15T15:31:49.357", "2015-04-15T21:15:40.123", "2015-04-15":

var mydata = [
        { id: "10",  invdate: "2015-04-15T15:31:49.357", name: "test1",... },
        { id: "20",  invdate: "2015-04-15T21:15:40.123", name: "test2",... },
        { id: "30",  invdate: "2015-04-15", name: "test3", ...},
        ...
    ]

The filtering by 15-Apr-2015 display all the three rows:

Another demo uses practically the same code, but displays the date in full date/time format. Nevertheless the filtering works. Be carefully, that I use the latest free jqGrid sources from GitHub in the demo. It's really needed because I made some small changes in the code of parseDate to make the demo working.