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?

Mallett

How will be the sorted order if you use the standard GMT date format? In my eyes jgrid uses it for sorting. – Wage

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.

Olive

@Gilbert Liddell: One more general remark. I see that you don't use your "votes" right which you have starting with 15 reputation points. I think you should know one important thing. Here you can find one simple rule: "As you see new answers to your question, vote up the helpful ones by clicking the upward pointing arrow to the left of the answer." The "accepted" and not voted questions will be interpret mostly as just "closed" and will be placed at the last place by the searching engine, because the answer is interpreted as not helpful. – Olive

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.

Kilburn

I have a date something like 9/24/2015 10:05:47 AM , 1/3/2015 6:53:00 AM so do you think the format should be m/d/yyyy h:ii:ss AmPm – Renitarenitent

I think it should be "m/d/yyyy h:i:s AmPm", but I could be wrong. Try it and see. According to their documentation, jqGrid uses PHP date formatting, so you can use this as a reference: php.net/manual/en/function.date.php – Kilburn

thanks for the reference, I used that format and it still messes up the sort. – Renitarenitent

as I have mentioned in the above comments for my date formats, after referring to the php link my format had to be n/j/Y g:i:s A but the Jqgrid does not even recognize it, What finally worked for me was m/d/Y h:i:s AmPm – Renitarenitent

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"}); } }

Islamite