Sorting UK Date format in JQuery Datatables DataTablesDataTables are a brilliant plugin for JQuery that restyle and add functionality to your tables. They add searching, pagination and sorting right out of the box with very little configuration needed – unfortunately the sorting doesn’t support the UK date format so you need to add your own sorting methods – basic ones are supplied by the site itself but I have added a couple of tweaks to handle blank dates.

I simply create a separate javascript file that I load after the main datatables javascript. The datatables require you define three functions for a new method of sorting.

  • Type detection – jQuery.fn.dataTableExt.aTypes.unshift
  • Sort Ascending – jQuery.fn.dataTableExt.oSort['type-asc']
  • Sort Descending – jQuery.fn.dataTableExt.oSort['type-desc']

I place all of these in the new javascript file. Instructions and example functions can be found on the datatables site, one page for the type detection and one for the sorting functions. The below functions are based on the code found here.

The Type detection function simply defines a way of checking for the type of sort you want, in this example that is done using a regular expression and when UK style dates are found it names then as “uk_date”.

The sorting functions use this name “uk_date” and provide simple functions for comparing one date against another. My addition here is to check for dates that contain both / and – delimiters and for blank dates. The functions simply return -1, 0 or 1 like most compare functions. The code for this is below, hope it helps somebody out.

// type detection - UK date
jQuery.fn.dataTableExt.aTypes.unshift(
	function ( sData )
	{
		if (sData !== null && sData.match(/^(0[1-9]|[12][0-9]|3[01])[\/-](0[1-9]|1[012])[\/-](19|20|21)\d\d$/))
		{
			return 'uk_date';
		}

		return null;
	}
);

// sorting - UK date
jQuery.fn.dataTableExt.oSort['uk_date-asc']  = function(a,b) {

	var ukDatea = a.split(/[\/-]/);
	var ukDateb = b.split(/[\/-]/);

	var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
	var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;

	if (isNaN(x)) { x = 0; }
	if (isNaN(y)) { y = 0; }

	console.log("sort: " + x + "," + y + ": " + ((x < y) ? -1 : ((x > y) ?  1 : 0)));

	return ((x < y) ? -1 : ((x > y) ?  1 : 0));
};

jQuery.fn.dataTableExt.oSort['uk_date-desc'] = function(a,b) {
	var ukDatea = a.split(/[\/-]/);
	var ukDateb = b.split(/[\/-]/);

	var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
	var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;

	if (isNaN(x)) { x = 0; }
	if (isNaN(y)) { y = 0; }

	return ((x < y) ? 1 : ((x > y) ?  -1 : 0));
};
  • Jim

    Just made a small change to the above code as some of the backslashes in the code were being stripped out by wordpress – so if you had problems with ordering the UK dates in your datatables with this code it may now work for you.

  • Julio

    nice. working perfect.
    greetings from Brazil

  • Guest

    sdasdad

  • AntZz

    Hi, I am having two of datetime format (d-m-Y H:i) columns in a table that using DataTable,
    and i want it to be sorted. How can I do it?
    Please do help me and your help will be much appreciated!
    Sorry for my bad english as well =)

    • http://www.jimcode.org Jim Rowe

      Hi AntZz, that is exactly what the code above is doing. It works by using a built in function of the datatables jQuery.fn.dataTableExt.aTypes.unshift to detect that format and provide ASC/DESC versions of sorts.

      • AntZz

        Sorry to say that i still cant get it work =(
        I don’t know how the exact way to invoke the method above,is it look like below:

        $(document).ready( function () { $(‘#table’).dataTable({ “bJQueryUI”: true, “sPaginationType”: “full_numbers”, “aoColumns”: [ null, null, { "aTypes" : "uk_date"}, { "aTypes" : "uk_date"}, null, null ] }); } );

        • http://www.jimcode.org Jim Rowe

          Hi AntZz, you should be able to just create your datatable in the same way as before (i.e. type of date not uk_date). The jQuery.fn.dataTableExt.aTypes.unshift call adds a regex check to the data and specifies that matching data should be considered “uk_date”. The date is then automatically sorted using the two sorting methods.

          Hope that helps.

  • gin(e)

    Good job, here reusing more code:

    jQuery.fn.dataTableExt.aTypes.unshift( function ( sData ){
    var date=null;
    if (sData !== null && sData.match(/^(0[1-9]|[12][0-9]|3[01])[/-](0[1-9]|1[012])[/-](19|20|21)dd$/))
    date=’it_date';

    return date;
    });

    jQuery.fn.dataTableExt.oSort['uk_date-asc'] = function(a,b) {
    return sort(a,b,’asc’);
    };

    jQuery.fn.dataTableExt.oSort['uk_date-desc'] = function(a,b) {
    return sort(a,b,’desc’);
    };

    function sort(a,b,ord){
    var m=['1','-1'],
    x = getEtoSort(a),
    y = getEtoSort(b);

    if(ord==’asc’)
    m=['-1','1'];

    return ((x y) ? m[1] : 0));
    }

    function getEtoSort(d) {
    var ukDatea = d.split(/[/-]/),
    e = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;

    if (isNaN(e))
    e = 0;

    return e;
    }

    It works for the uk-date manipulated whit mRender to look like italian date.