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.

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.

• javascript
• jquery
• datatables
• momentjs

• Are the two formats you mention in your question the only two date formats that will ever be in play? Also, which of the following two possibilities is true in your case? Possibility 1. A table may show dates in various formats but in a single table, only one format will be used. Possibility 2. A table may show dates in various formats and a single table can contain multiple formats at once. So one row would use the US format and the next row would use the German format. You should edit your question to clarify all this. – Louis Commented Apr 8, 2019 at 15:00
• @Louis: You are right, that's important... In my case, possibility 2 is the right one. A table may show dates in various formats but in a single table, only one format will be used. At the moment I don't know how many different formats the application will support. I guess 5+. I've edited the question above. – JonSnow Commented Apr 8, 2019 at 15:34
• @JonSnow: to avoid the confusion, which Louis refers to (and which, by the way, I have mentioned in my answer about a week ago), you may use your own date format parser which I shared in the last ment to my answer. That script, most times, may distinguish days from months based on the context (all possible values for both). – Yevhen Horbunkov Commented Apr 10, 2019 at 7:32

6 Answers 6

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 pared 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.

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./jquery-3.3.1.min.js"></script>
  <script type="application/javascript" src="https://cdn.datatables/1.10.19/js/jquery.dataTables.min.js"></script>
  <script type="application/javascript" src="https://cdnjs.cloudflare./ajax/libs/moment.js/2.8.4/moment.min.js"></script>
  <script type="application/javascript" src="https://cdn.datatables/plug-ins/1.10.19/sorting/datetime-moment.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables/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 mon 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 e 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;
}

You can also use an array for multiple formats as momentjs suggest here

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

const srcData = [{"name":"Freda Rasmussen","date":"03-01-2015","date2":"03.01.2015","date3":"03/01/2015"},{"name":"Ramsey Blackwell","date":"08-22-2016","date2":"08.22.2016","date3":"08/22/2016"},{"name":"Cameron Leach","date":"11-01-2015","date2":"11.01.2015","date3":"11/01/2015"},{"name":"Foley Porter","date":"04-26-2014","date2":"04.26.2014","date3":"04/26/2014"},{"name":"Corrine Wiggins","date":"04-18-2018","date2":"04.18.2018","date3":"04/18/2018"}]

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


const dataTable = $('#mytable').DataTable({
  data: srcData,
  columns: [{
      title: 'name',
      data: 'name'
    },
    {
      title: 'date',
      data: 'date'
    },
    {
      title: 'date',
      data: 'date2'
    },
    {
      title: 'date',
      data: 'date3'
    }
  ]

});

<script src="https://cdnjs.cloudflare./ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="application/javascript" src="https://cdn.datatables/1.10.19/js/jquery.dataTables.min.js"></script>
<script type="application/javascript" src="https://cdnjs.cloudflare./ajax/libs/moment.js/2.8.4/moment.min.js"></script>
<script type="application/javascript" src="https://cdn.datatables/plug-ins/1.10.19/sorting/datetime-moment.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables/1.10.19/css/jquery.dataTables.min.css">

<table id="mytable"></table>

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

That's US formatting. British dates use dd/mm/yyyy formatting.

Because of this, what you ask is impossible. Consider "01/04/2019". Is that January 4th? Or is it April 1st? There's know way to know from the string alone. You must supply the locale context yourself.

See also: Date formats by country on Wikipedia.

as I understand you want to set something default? A default location? To do this, do it this way: the function is defined as $ .fn.dataTable.moment = function (format, locale). You add your location as the second parameter. In your case, ** moment.js ** is used. By default, Moment.js es with English (United States) locale strings. If you need other locations, you can load them into Moment.js for later use.

To load a locale, pass the key and string values ​​to moment.locale.

More details on each part of the language pack can be found in the customization section.

Without a more significant sample size, detecting the date format of a given date with any accuracy can range from the simple to the impossible.

You'll need to pass the locale in use to any formatting or parsing function to get any sort of accuracy but do bear in mind what could happen if multiple formats have to be used by different users at the same time.

What I remend is to use UTC format (YYYY-MM-DD) for data storage, as it's pletely unambiguous and convert from/to the user locale on input/display. This way the displayed format can be changed by the end user without any adverse effect on the integrity of the data stored.