jQuery DataTables: is there a way to detect date format automatically?

Question

I have a datatable up and running and at the moment I have defined the date format manually by adding

$.fn.dataTable.moment("DD.MM.YYYY");

before defining my datatable itself:

var myTable = $('#authors').DataTable({
    "paging": false,
    "ordering": true,
    "order": [2, "desc"],
    "info": false,
    "stateSave": true,
    "responsive": true,
    "columnDefs": [
        { targets: "noSort", orderable: false }
    ]       
});

As you can see, we are currently using the German date format. But it can be the case that we have to use other formats later that year.

Is there a way to detect the value of a given date column automatically so that I can sort that column correctly? Or will I always have to define the date format manually?

What I want to have is kind of a dynamic version of the line

$.fn.dataTable.moment("DD.MM.YYYY");

It should detect, "oh, the value in that column is '29.04.2019', this is a German date format, defined as dd.mm.yyyy" and use this format for further sorting.

Or if the value is "04/29/2019" instead, this should be recognized as a US date format, using 'mm/dd/yyyy' for sorting.

At the moment I don't know how many different date formats the application will support. I guess it will be 5 or more. But in a single table, only one format will be used.

Answer 1

It's been suggested that you pass an array for formats to $.fn.dataTable.moment(...), but this works if and only if it can never happen that a data match more than one format in the array. Unless you can guarantee this, then passing an array of formats is not the solution.

You started with the example of DD.MM.YYYY and MM/DD/YYYY. A date will match either one format or the other but not both because if it has period delimiters than it matches the 1st format but not the 2nd and if it has slashes delimiters it matches the 2nd format but not the 1st. However, in general if you have dates from somewhere else than the US or Germany, you'll run into ambiguous cases. Matt Johnson mentioned for instance a date like "01/04/2019" which can fit the MM/DD/YYYY format and be interpreted as "January 4th 2019", or fit the DD/MM/YYYY format and be interpreted as "1 April 2019".

If you can have dates in either DD/MM/YYYY or MM/DD/YYYY format and you call $.fn.dataTable.moment(["DD/MM/YYYY", "MM/DD/YYYY"]) then you will sometimes get incorrect results. The problem is that the plugin that implements the function you're calling looks at each cell in isolation.

Table 1

Suppose a table meant to use dates in the DD/MM/YYYY format, with the following cells:

1. 21/2/2019
2. 1/4/2019
3. 24/12/2019

Table 2

Suppose a table meant to use dates in the MM/DD/YYYY format, with the following cells:

1. 2/21/2019
2. 4/1/2019
3. 12/24/2019

The two tables actually contain the same dates. They are just represented differently.

Suppose you configured your table with $.fn.dataTable.moment(["DD/MM/YYYY", "MM/DD/YYYY"]). Table 1 will be interpreted correctly. However, row 2 in table 2 won't be interpreted correctly. The date 4/1/2019 does fit the first format in the array (DD/MM/YYYY) and this is how moment will interpret it. It does not matter how many other cells cannot fit DD/MM/YYYY because the plugin that calls moment does not do a statistical analysis. It looks at each cell in isolation. Here's the relevant code (with some blank lines removed):

$.fn.dataTable.moment = function ( format, locale, reverseEmpties ) {
    var types = $.fn.dataTable.ext.type;

    // Add type detection
    types.detect.unshift( function ( d ) {
        if ( d ) {
            // Strip HTML tags and newline characters if possible
            if ( d.replace ) {
                d = d.replace(/(<.*?>)|(\r?\n|\r)/g, '');
            }

            // Strip out surrounding white space
            d = $.trim( d );
        }

        // Null and empty values are acceptable
        if ( d === '' || d === null ) {
            return 'moment-'+format;
        }

        return moment( d, format, locale, true ).isValid() ?
            'moment-'+format :
            null;
    } );

    // Add sorting method - use an integer for the sorting
    types.order[ 'moment-'+format+'-pre' ] = function ( d ) {
        if ( d ) {
            // Strip HTML tags and newline characters if possible
            if ( d.replace ) {
                d = d.replace(/(<.*?>)|(\r?\n|\r)/g, '');
            }

            // Strip out surrounding white space
            d = $.trim( d );
        }

        return !moment(d, format, locale, true).isValid() ?
            (reverseEmpties ? -Infinity : Infinity) :
            parseInt( moment( d, format, locale, true ).format( 'x' ), 10 );
    };
};

You could flip the arguments and call $.fn.dataTable.moment(["MM/DD/YYYY", "DD/MM/YYYY"]). Now the 2nd table would be fine, but the same problem would happen in the 1st table.

---

Ok, what then?

If the backend happens to already contain UTC time stamps, then I'd just send these time stamps to the front end instead of sending localized values. At the stage of rendering a cell that contains a date, I'd have the front end convert the UTC date to a format that makes sense to the user. Datatable would do sorting on the basis of the UTC values, which can be compared without ambiguity.

If the backend does not stores its dates as UTC time stamps, I'd redesign it so that it does and then do what I described in the previous paragraph.

Otherwise, there may be a way to do in the front end a statistical analysis of your table prior to Datatables trying to render and order it. So you could discover which format is used and then feed this to Datatables. However, this still seems brittle to me. If the table is using the server-side protocol, then only a small portion of the data is available at a time. If you make an analysis only on the first response from the server, a later response covering a later portion of the table may disprove the initial assumption. Moreover, there could be cases where all the dates in a datatable are ambiguous. On a large and unfiltered dataset this may be unlikely but as soon as users are allowed to filter the dataset to show only a subset, they may filter it in a way that results in all dates in a specific subset being ambiguous. I would not deploy an application with the hope that this will never happen.

Answer 2

Assuming, you already use date sorting plug-in, you don't need to worry about anything as long as your desired format is among specified:

$.fn.dataTable.moment(['MM/DD/YYYY', 'DD-MM-YYYY', 'DD-MMM-YYYY']);

Following rules dictated by moment.js, which your plug-in relies on, must be obeyed.

Below you may find the working demo:

//table source
const srcData = [
	{name: 'Til Schweiger', date: '19-12-1963'},
	{name: 'Jan Joseph Liefers', date: '08/08/1964'},
	{name: 'Moritz Bleibtreu', date: '13-Aug-1971'},
	{name: 'Thomas Jahn', date: '07/08/1965'}
		
];

$.fn.dataTable.moment(['MM/DD/YYYY', 'DD-MM-YYYY', 'DD-MMM-YYYY']);

//DataTable init
const dataTable = $('#mytable').DataTable({
	dom: 't',
	data: srcData,
	columns: [
		{title: 'name', data: 'name'},
		{title: 'date', data: 'date'}
	]
	
});

<!doctype html>
<html>
<head>
  <script type="application/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script type="application/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
  <script type="application/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
  <script type="application/javascript" src="https://cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
</head>
<body>
<table id="mytable"></table>
</body>
</html>

However, unexpected behavior may occur if you have dates where both days and months are within 0-12 range and both 'DD/MM/YYYY' and 'MM/DD/YYYY' are valid, so I guess, that it would be safer to use preformatted dates (using common format) and corresponding $.fn.dataTable.moment() setting for that format.

If someone, unlike OP, might need an actual way to detect date format and not long-winded justification of why not to do that client-side, following home baked format parser may come in handy:

//sample source data
const test1 = ['01-05-2015', '21-06-1982', '13-08-1982', '05-06-2018'];
const test2 = ['05/01/2015', '06/21/1982', '08/13/1982', '06/05/2018'];
const test3 = ['01/05/2015', '21/06/1982', '13/08/1982', '05/06/2018'];
const test4 = ['1-May-2015', '21-Jun-1982', '13-Aug-1982', '5-Jun-2018'];

const dateFormatRecognition = dateArr => {
	//split each date string into parts, delimited by either of ('.', '-', '/')
	let dateParts = dateArr.map(testdate => testdate.split(/[\/\.\-]/));
	//regroup parts so, first, second and third parts values groupped within corresponding array
	dateParts = dateParts[0].map((entry, colindex) => dateParts.map(col => col[colindex]));
	//check each part values against the set of criteria and figure out possible options
	const partsFormat = dateParts.map(parts => ({
		//check whether each part values could be day, month, year
		daysNum: parts.every(part => /^\d+$/.test(part) && part > 0 && part < 32),
		monthAlpha: parts.every(part => ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'].indexOf(part.toLowerCase()) > -1),
		monthNum: parts.every(part => /^\d+$/.test(part) && part > 0 && part < 13),
		yearsNum: parts.every(part => /^\d+$/.test(part) && part > 31),
		//grap min parts length
		minLen: parts.reduce((min,part) => part.length < min ? part.length : min, parts[0].length),
	}));
	//grab first of possible delimiters ('.', '-', '/') and check if those are the same across all values
	const delimiter = dateArr.every(dateEntry => dateEntry.match(/[\.\-\/]/)[0] == dateArr[0].match(/[\.\-\/]/)[0]) ? dateArr[0].match(/[\.\-\/]/)[0] : null;
	//decision making about parts roles
	return partsFormat.reduce((format, partProps) => {
		format.push(partProps.yearsNum ? 'YYYY' :
		partProps.monthNum && format[0] != 'MM' && partProps.minLen == 2 ? 'MM' :
		partProps.monthNum && format[0] != 'MM' && partProps.minLen == 1 ? 'M' :
		partProps.monthAlpha ? 'MMM' :
		partProps.daysNum && partProps.minLen == 2 ? 'DD' :
		partProps.daysNum && partProps.minLen == 1 ? 'D' : null);
		return format;
	}, []).join(delimiter);
};
//output test array formats
console.log(dateFormatRecognition(test1));
console.log(dateFormatRecognition(test2));
console.log(dateFormatRecognition(test3));
console.log(dateFormatRecognition(test4));

.as-console-wrapper {
  max-height: 100% !important;
  top: 0;
}