Filter 'Equal' Condition Not Working as Expected in v10.1.0 for datetime columns

[devt]

Hi Support Team,

Could you please look into why I can no longer use the previous "equal" condition that ignores time in v10.1.0? For some reason, it shows no rows even though there is data in the grid that matches the applied filter.

The issue can be reproduced by modifying the "Order DateTime" column to use the "equal" condition filter in version <= v7 and setting the grid to use fmtDate: "dd-mmm-yyyy".

          {
              title: "Order DateTime", minWidth: "230", dataIndx: "OrderDate", dataType: "date",
                //exportRender: true,
                filter: {
                    crules:[{condition: "equal"}],                   
                    conditions: {
                        equal: {
                            compare: function(cd, val){
                                //debugger;
                                cd = new Date(cd);
                                val = new Date(val);
                        //console.log(val);
                                //ignore time.
                                if(cd.getDate()==val.getDate() && cd.getMonth()==val.getMonth() && cd.getYear()==val.getYear()){
                                    return true;
                                }
                            }
                        }
                    }
                },
                filterFn: function(ui){
                    ///debugger;
                    if(ui.condition != 'range'){
                        return {
                            //override the column format
                            format:'M dd, yy'
                        }
                    }
                }
          },

Please advise. Thanks!

[paramvir]

As mentioned in your post, I've added fmtDate: "dd-mmm-yyyy" and use your column definition for Order Date in this jsfiddle.

https://jsfiddle.net/4aLsdhy8/2/

it works fine in v10.1.0, note that I've added fmtDateFilter: "dd-mmm-yyyy" to match it with fmtDate

Please advise.

[devt]

It doesn't work for me; the wrong date is still being applied for filtering. The fmtDate filter only changes the format of the date in the filter cell.

When I log the value, it's passing an incorrect date for comparison. The date value seems to be converted to UTC even though I'm in CST.

[paramvir]

I'm able to reproduce the issue and looking into it.

[paramvir]

Please add offset in the compare function for now to make the dates comparable.

Code: [Select]

            compare: function (cd, val) {
              var offset = new Date(val).getTimezoneOffset() * 60 * 1000

              val += offset

              if (cd - val >= 0 && cd - val < 24 * 3600 * 1000) {
                return true
              }
            },

This compare function assumes that all dates in column are in datetime format while the date entered in filter box is date only. If dates in column are also dates ( without time ), then don't add offset to the val.

https://jsfiddle.net/erpmgxto/3/

[devt]

I've tried your fix, and it works, but when the offset is applied to date-only columns, it still works as well. For example, it works with ShippedDate, which is a date-only column, even though the offset ideally should not apply here, based on your explanation. Could you please advise on why this happens?

Additionally, is this considered a permanent fix for the issue, or should we treat it as a temporary workaround? I?d prefer not to commit a temporary solution to production code.

[paramvir]

"yyyy-mm-ddThh:mm" and "yyyy-mm-dd" are parsed differently hence the requirement to add timezone offset manually so as to make them comparable. offset is not to be used when both filter value and column values are in same format. While it may work with/without offset for some timezones, the offered solution is for all timezones.

It's a temporary workaround and might be fixed in upcoming version so that above 2 formats are parsed in same way before passing as arguments to filter compare callback.