How can I get Bootgrid to sort by UK date?

Question

My dates are in standard MySql database yyyy/mm/dd format and I re-format to UK format in the table as dd/mm/yyyy, but then I find jquery bootgrid table doesn't sort correctly, no doubt because it's expecting mm/dd/yyyy US format.

Is there a fix for this? Do I need to format as a raw date in the table and then use a bootgrid formatter to format as a UK date?

Answer 1

Store your dates in the YYYYMMDD format and use a Bootgrid formatter like this:

$("#grid-basic").bootgrid(
{
    formatters:
    {
        "dateformatter": function (column, row)
        {
            var strDate = row.mydate.substring(6, 9) + "/" + row.mydate.substring(4, 6) + "/" + row.mydate.substring(0, 4);
            return strDate;
        }
    }
});

just remembering to add the data-formatter="dateformatter" attribute to your date column.

The only contraindication I know is Bootgrid columns with formatters are not found by the default search feature. Hoping someone posts a solution for this.

Answer 2

I've referenced moment.js from http://momentjs.com set data-formatter="ukdate" in the table header and used the following Bootgrid formatter:

            formatters: {
                "ukdate": function(column, row)
                {
                    return moment.utc(row[column.id]).format('DD-MM-YYYY HH:mm');
                }
            }

I find that sorting by setting data-order="desc" works when the date is the only one with data-order set but not with multiple columns with data-order set.