Date column sorting not working for H24 format

Majestic

Hello,
I use a simpleDateFormat "yyyy-MM-dd hh:mm:ss.SSS" to display a date in my datatable. I've to change this format to "yyyy-MM-dd HH:mm:ss.SSS" to have the 24H format.
But with this new format, the sort on date column is not working anymore, instead of a sort by date, it is doing an alphabetical sort. Although it was working with the previous format.
It is a matter of plugin I have to use or about code ?

This is my datatable :

$('#dataTables').DataTable({
    responsive: false,
    ajax: {
        "url": restURI + 'orch/search/events/',
        "contentType": "application/json",
        "type": "POST",
        "data": function ( d ) {
            return JSON.stringify(getDatasToSearch());
        },
        error: function(jqXHR, textStatus, errorThrown){
            manageAjaxError(jqXHR, textStatus, errorThrown)
        }
    },
    buttons: [
     'selectAll',
     'selectNone'
     ],
     language: {
         buttons: {
             selectAll: "Select all items",
             selectNone: "Select none"
         }
     },
     select: {
        selector: 'td:first-child',
        style: 'multi'
    },
    order: [0, 'desc'],
    scrollCollapse: true,
    scrollX: false,
    aLengthMenu: [
        [10, 25, 50, 100, -1],
        [10, 25, 50, 100, "All"]
    ],
    iDisplayLength: 25,
    "columns": [
        { "data": "EVENT_ID", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "FLOW_ID", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "FLOW_NAME", "defaultContent": "", "sClass": "text-center limitedWidthFlNm" },
        { "data": "OBJECT_TYPE", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_NAME", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_VERSION", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_ITERATION", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_NAME", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_STATUS", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_MESSAGE", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "CREATE_DATE", "defaultContent": "", "sClass": "text-center limitedWidth","sType":"date" },
        { "data": "UPDATE_DATE", "defaultContent": "", "sClass": "text-center limitedWidth " ,"sType":"date" }
    ]
})

Thank you for your help !

kthorngren

This Date Time Sorting Blog should help.

Kevin

Majestic

Thank you for your quick replay. I saw this pluging, but when I use it I have an error :

Uncaught TypeError: Cannot read property 'length' of null
at jquery.dataTables.min.js:48
at i (jquery.dataTables.min.js:35)
at Object.success (jquery.dataTables.min.js:35)
at i (jquery.min.js:2)
at Object.fireWith [as resolveWith] (jquery.min.js:2)
at A (jquery.min.js:4)
at XMLHttpRequest.<anonymous> (jquery.min.js:4)

This is my datatable definition :

$.fn.dataTable.moment( 'yyyy-MM-dd HH:mm:ss:SSS' );
$('#dataTables').DataTable({
    responsive: false,
    ajax: {
        "url": restURI + 'orch/search/events/',
        "contentType": "application/json",
        "type": "POST",
        "data": function ( d ) {
            return JSON.stringify(getDatasToSearch());
        },
        error: function(jqXHR, textStatus, errorThrown){
            manageAjaxError(jqXHR, textStatus, errorThrown)
        }
    },
    buttons: [
     'selectAll',
     'selectNone'
     ],
     language: {
         buttons: {
             selectAll: "Select all items",
             selectNone: "Select none"
         }
     },
     select: {
        selector: 'td:first-child',
        style: 'multi'
    },
    order: [0, 'desc'],
    scrollCollapse: true,
    scrollX: false,
    aLengthMenu: [
        [10, 25, 50, 100, -1],
        [10, 25, 50, 100, "All"]
    ],
    iDisplayLength: 25,
    "columns": [
        { "data": "EVENT_ID", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "FLOW_ID", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "FLOW_NAME", "defaultContent": "", "sClass": "text-center limitedWidthFlNm" },
        { "data": "OBJECT_TYPE", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_NAME", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_VERSION", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_ITERATION", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_NAME", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_STATUS", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_MESSAGE", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "CREATE_DATE", "defaultContent": "", "sClass": "text-center limitedWidth","sType":"date" },
        { "data": "UPDATE_DATE", "defaultContent": "", "sClass": "text-center limitedWidth " ,"sType":"date" }
    ]
})

This is the script import :

<!-- jQuery -->
<script th:src="@{/vendor/jquery/jquery.min.js}"></script>

<!-- Bootstrap Core JavaScript -->
<script th:src="@{/vendor/bootstrap/js/bootstrap.min.js}"></script>

<!-- Metis Menu Plugin JavaScript -->
<script th:src="@{/vendor/metisMenu/metisMenu.min.js}"></script>

<!-- DataTables JavaScript -->
<script th:src="@{/vendor/datatables/js/jquery.dataTables.min.js}"></script>
<script th:src="@{/vendor/datatables-plugins/dataTables.bootstrap.min.js}"></script>
<script th:src="@{/vendor/datatables-responsive/dataTables.responsive.js}"></script>
<script th:src="@{/vendor/datatables/js/dataTables.select.min.js}"></script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>

Did I do something wrong in use ?

kthorngren

Uncaught TypeError: Cannot read property 'length' of null

My guess is with something in the data being returned from the Ajax request. Not sure though.

Not that this will fix the problem but I don't think your datetime format is correct for moment.js. This [doc[(http://momentjs.com/docs/#/displaying/) contains the format strings supported. At least for the data I think you will want YYYY-MM-DD. Didn't check the time

Another option would be to use [Orthogonal Data]((https://datatables.net/manual/data/orthogonal-data) and convert the datetime to a timestamp for the sort type.

Kevin

Majestic

You were right Kevin, something was wrong with the data. On java side, I've made a mistake on format, I wrote :
-YYYY-MM-DD HH:mm:ss:SSS
instead of
-YYYY-MM-DD HH:mm:ss.SSS (colon instead of dot between ss and SSS).

The plugin and the sort are working now ! Thank you for your help Kevin. Awesome !