jqGrid not sorting dates correctly

Question

I have an issue when sorting dates using jqGrid. Given the following dates

jqGrid is sorting my dates as follows:

01/01/2010
01/01/2011
01/02/2010
01/02/2011

I would expect to see

01/01/2010
01/02/2010
01/01/2011
01/02/2011

My date format is dd/mm/yyyy and I have the follow format options -

{
    name: 'myDate',
    index: 'myDate',
    sortable: true,
    sorttype: 'date'
}

Have I missed something here or is this a limitation of the jqGrid?

Answer 1

You made the typical error. The problem is that jqGrid expects the input of dates in the ISO 8601 date format: Y-m-d.

If you post the input data of jqGrid in the "dd/mm/yyyy" format you should add datefmt: 'm/d/Y' property to the column definition.

The best way would be to fill the date data of the grid in the ISO format and to convert the input in any other format only to display the date in jqGrid using the following settings

formatter:'date', formatoptions: {newformat:'m/d/Y'}, datefmt: 'd-M-Y'

Compare the source code of the tree demos: this, this and this.

Answer 2

jqGrid does not support sorting by datetime but by just date.

Actually, I've found that jqGrid does support sorting by datetimes.

We have a JSON web service where the dates were (annoyingly) arriving in this format:

'2/24/2015 9:48:04 AM'

jqGrid was perfectly capable of sorting by date & time using this:

colModel: [
  { name: "dt", sorttype: 'date', datefmt: 'm/d/yyyy h:i:s AmPm' }, 

This particular app was using jqGrid 4.4.5 (from 2008) so this isn't a new feature.

Hope this helps.

Answer 3

jqGrid does not support sorting by datetime but by just date. Hence you can use alternative as given in its PDF i.e. shown in below example. If your data in grid is already sorted by Date and Time , and you also have a column with the Index of all rows like in Numbers as 1, 2, 3, .... n . The you can sort the datetime on the Index Column. This will always ensure asc or desc order for datetime.

You can sort the jqGrid's DATE and time or date by another Column content. Such as given in example below on onSortCOl:

        onSortCol: function(name,index) { if(name == 'createDateTime') { jQuery("#viewNotesGrid").setGridParam({sortname:"ID"}); } }