Initial ordering by date

linksdesign

I have two columns in my table which are dates (info comes from a mySQL database and the field types are DATE for "Completion Date" and TIMESTAMP for "Date Paid" in the standard format yyyy-mm-dd). I want to set the initial sort order to be by Date Paid with the most recent first. I set it to order by column 6, descending as follows:

"order": [ 6, 'desc' ],

but it's only sorting by the day and not taking into account the month, so for example 18 November comes before 6 December. A test case is here: http://live.datatables.net/buluseni/2/edit. How would I change it to display in the correct date order?

kthorngren

This datetime sorting doc provides the recommended way to sort dates using the moment.js library.

Kevin

linksdesign

I've updated the test case http://live.datatables.net/buluseni/4/edit and included the moment.js files along with the following line of code:

$.fn.dataTable.moment( 'd M Y' );

As the date info is coming from the database and then formated using PHP, I wasn't sure whether the date format should be as above or in the standard UNIX format yyyy-mm-dd, but I've tried both but it's still not working. Likely I've put the code in the wrong place, could someone help me to get the syntax correct?

kthorngren

The blog post points to the Moment documentation which has the format strings. Also you need to put the $.fn.dataTable.moment( 'd M Y' ); ahead of the Datatables initialization.

The format needed is $.fn.dataTable.moment( 'DD MMM YYYY' );. Here is the updated example:
http://live.datatables.net/buluseni/5/edit

Kevin

linksdesign

That's fantastic, works like a dream - many thanks for your help!